p2p.wrox.com: Archives - asp

asp_databases thread: Help with dynamic stored procs

Message #1 by ckoski@w... on Thu, 13 Jul 2000 10:07:08 -0400

Hi,



I've been designing a bunch of business objects and I've come across a minor

roadblock...  I'm trying to implement a dynamic stored procedure to make my

access a lot faster than passing a dynamic SQL statement through ADO.

Here's my stored proc code:



Create Procedure content_get_title

 (@content_id int, @table_name varchar(255), @field_name varchar(255))

As

 Declare @SQLStatement varchar(255)

 SELECT @SQLStatement = "SELECT " + @field_name + " FROM " + @table_name + "

WHERE cid = " + @content_id

 EXEC (@SQLStatement)

 return



I'm using SQL Server 6.5, and I'm trying to follow the example from 4 Guys

From Rolla, and I keep getting error messages every time I try to save the

stored procedure...  I know it's syntactical, but I have no idea what the

proper syntax for SQL Server 6.5 SPs are to do this...  the error message

when I save this is:



ADO error [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name

'SELECT '.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ' FROM '.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ' WHERE

cid = '



I totally stuck, and I've tried all the ways to implement this using the

example from http://www.4guysfromrolla.com/webtech/020600-1.shtml and I am

at a loss... I am pretty sure the example they supplied me with works with

SQL Server 7.0, but how can I do with with SQL Server 6.5?



Thanks in advance,



Cory



**************************************************

Cory Koski

Website Programmer

Wabang Creative Technology

Brainbench e-certified Master ASP programmer

(xxx) xxx-xxxx

Message #2 by "joern saischek" <joern.saischek@c...> on Fri, 14 Jul 2000 13:1:56

Hi,

try to replace " with ' in your SELECT



(worked with 7.0)



SELECT @SQLStatement = 'SELECT ' + @field_name + ' FROM ' + @table_name + ' 

WHERE cid = ' + @content_id





On 07/13/00, Cory Koski wrote:

> Hi,

> 

> I've been designing a bunch of business objects and I've come across a minor

> roadblock...  I'm trying to implement a dynamic stored procedure to make my

> access a lot faster than passing a dynamic SQL statement through ADO.

> Here's my stored proc code:

> 

> Create Procedure content_get_title

>  (@content_id int, @table_name varchar(255), @field_name varchar(255))

> As

>  Declare @SQLStatement varchar(255)

>  SELECT @SQLStatement = "SELECT " + @field_name + " FROM " + @table_name + "

> WHERE cid = " + @content_id

>  EXEC (@SQLStatement)

>  return

> 

> I'm using SQL Server 6.5, and I'm trying to follow the example from 4 Guys

> From Rolla, and I keep getting error messages every time I try to save the

> stored procedure...  I know it's syntactical, but I have no idea what the

> proper syntax for SQL Server 6.5 SPs are to do this...  the error message

> when I save this is:

> 

> ADO error [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name

> 'SELECT '.

> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ' FROM '.

> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ' WHERE

> cid = '

> 

> I totally stuck, and I've tried all the ways to implement this using the

> example from http://www.4guysfromrolla.com/webtech/020600-1.shtml and I am

> at a loss... I am pretty sure the example they supplied me with works with

> SQL Server 7.0, but how can I do with with SQL Server 6.5?

> 

> Thanks in advance,

> 

> Cory

> 

> **************************************************

> Cory Koski

> Website Programmer

> Wabang Creative Technology

> Brainbench e-certified Master ASP programmer

> (xxx) xxx-xxxx

Message #3 by ckoski@w... on Fri, 14 Jul 2000 09:26:23 -0400

ok, I fixed it... thank you for you help...  originally, SQL Server would

complain about trying to implicitly covert my @content_id from type "int" to

type "varchar"...  I used the CONVERT function wrong, and I didn't realize

it, so the SP would never save, so I gave up...I've now looked up the

CONVERT function in my MSDN CDs and I figured out the correct syntax.



Thank you for reminding me that it is good to look in the Microsoft

documentation from time to time :)  I recommend that everyone does this from

time to time... it's good practice, and the more you do it, the better you

get at trying to understand MS's documentation :)



Cory





----- Original Message -----

From: "joern saischek" 

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, July 14, 2000 1:00 PM

Subject: [asp_databases] Re: Help with dynamic stored procs





> Hi,

> try to replace " with ' in your SELECT

>

> (worked with 7.0)

>

> SELECT @SQLStatement = 'SELECT ' + @field_name + ' FROM ' + @table_name +

'

> WHERE cid = ' + @content_id

>

>

> On 07/13/00, Cory Koski wrote:

> > Hi,

> >

> > I've been designing a bunch of business objects and I've come across a

minor

> > roadblock...  I'm trying to implement a dynamic stored procedure to make

my

> > access a lot faster than passing a dynamic SQL statement through ADO.

> > Here's my stored proc code:

> >

> > Create Procedure content_get_title

> >  (@content_id int, @table_name varchar(255), @field_name varchar(255))

> > As

> >  Declare @SQLStatement varchar(255)

> >  SELECT @SQLStatement = "SELECT " + @field_name + " FROM " + @table_name

+ "

> > WHERE cid = " + @content_id

> >  EXEC (@SQLStatement)

> >  return

> >

> > I'm using SQL Server 6.5, and I'm trying to follow the example from 4

Guys

> > From Rolla, and I keep getting error messages every time I try to save

the

> > stored procedure...  I know it's syntactical, but I have no idea what

the

> > proper syntax for SQL Server 6.5 SPs are to do this...  the error

message

> > when I save this is:

> >

> > ADO error [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column

name

> > 'SELECT '.

> > [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '

FROM '.

> > [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '

WHERE

> > cid = '

> >

> > I totally stuck, and I've tried all the ways to implement this using the

> > example from http://www.4guysfromrolla.com/webtech/020600-1.shtml and I

am

> > at a loss... I am pretty sure the example they supplied me with works

with

> > SQL Server 7.0, but how can I do with with SQL Server 6.5?

> >

> > Thanks in advance,

> >

> > Cory

> >

> > **************************************************

> > Cory Koski

> > Website Programmer

> > Wabang Creative Technology

> > Brainbench e-certified Master ASP programmer

> > (xxx) xxx-xxxx

>

Return to Index

P2P Archives