exec() select statement longer than 255 characters

exec() select statement longer than 255 characters

Post by Heather Hainde » Sat, 25 Mar 2000 04:00:00



I have a dynamic query that I need to execute in a stored proc in SQL
Server 6.5. The query is longer than 255 characters.
Using exec(), is there a way to execute this query? What are my options?

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

exec() select statement longer than 255 characters

Post by Patrick Loga » Sat, 25 Mar 2000 04:00:00


Might check BOL for sp_executesql

--
Patrick Logan, MCSD
Senior Technical Development Advisor
McKessonHBOC -- Extended Care Solutions Group

Opinions expressed are my own and not necessarily those of McKessonHBOC.

 
 
 

exec() select statement longer than 255 characters

Post by Bill Ki » Sat, 25 Mar 2000 04:00:00


sp_execsql is new for SQL 7.0

Bill Kim


> Might check BOL for sp_executesql

> --
> Patrick Logan, MCSD
> Senior Technical Development Advisor
> McKessonHBOC -- Extended Care Solutions Group

> Opinions expressed are my own and not necessarily those of McKessonHBOC.

 
 
 

exec() select statement longer than 255 characters

Post by Bill Ki » Sat, 25 Mar 2000 04:00:00


You can concatnate multiple variables for the exec statement. Here is a
sample



/*** build sql statements



Bill Kim


Quote:> I have a dynamic query that I need to execute in a stored proc in SQL
> Server 6.5. The query is longer than 255 characters.
> Using exec(), is there a way to execute this query? What are my options?

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

exec() select statement longer than 255 characters

Post by Robert De Santi » Sat, 25 Mar 2000 04:00:00


Heather,
Try breaking up the string and assigning to multiple variables; then
concatenate the variables in the exec() statement.

Example:








Cheers,
Robert


Quote:> I have a dynamic query that I need to execute in a stored proc in SQL
> Server 6.5. The query is longer than 255 characters.
> Using exec(), is there a way to execute this query? What are my options?

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

exec() select statement longer than 255 characters

Post by Heather Hainde » Sat, 25 Mar 2000 04:00:00


Thank you guys both for your help!
I had the right idea...maybe my error is happening somewhere else. I
cut my SQL into three variables, then execute it this way. The error I
get when I try to create the stored proc is:


Here is my code:
create procedure dbo.usp_dcoecnsearch

)
as




p.processname, b.fabname, v.quarter, v.originatorname from
vw_dcoecnlist v, tbl_tooltype t, tbl_fa f, tbl_process p, tbl_fab b'




v.tooltypeid=t.tooltypeid and v.faid=f.faid and v.processid=p.processid
and v.fabid=b.fabid'



> Heather,
> Try breaking up the string and assigning to multiple variables; then
> concatenate the variables in the exec() statement.

> Example:








> Cheers,
> Robert



> > I have a dynamic query that I need to execute in a stored proc in
SQL
> > Server 6.5. The query is longer than 255 characters.
> > Using exec(), is there a way to execute this query? What are my
options?

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

exec() select statement longer than 255 characters

Post by Heather Hainde » Sat, 25 Mar 2000 04:00:00


Never mind. I found the difference...it's working.

Thanks again for the help!


> You can concatnate multiple variables for the exec statement. Here is
a
> sample



> /*** build sql statements



> Bill Kim



> > I have a dynamic query that I need to execute in a stored proc in
SQL
> > Server 6.5. The query is longer than 255 characters.
> > Using exec(), is there a way to execute this query? What are my
options?

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.