Problem with Stored Procedure Syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Stored Procedure Syntax

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-11-07 : 01:33:31

My goal is to use the parameter values incoming in my select statement to bring back data. I am passing in parameters from my ASP.NET app so this stored proc can be very dynamic in that it can select the field names from whatever table I passed to the stored proc's parameters. I know I can't directly do this using parameters but not sure how to form my sql statement so I tried using variables with no luck also.

ALTER PROCEDURE Get_DropDown_ListValues

@TableName varchar(40),
@ValueField varchar(40),
@DisplayField varchar(40)

AS

DECLARE @ValueF varchar(40),
@DisplayF varchar(40),
@TableN varchar(40)

Set @ValueF = @TableName
Set @DisplayF = @ValueField
Set @TableN = @DisplayField

Select @ValueF, @DisplayF FROM @TableN ORDER BY @DisplayF ASC

I get the error: Must declare the variable '@TableN'.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 01:39:29

quote:
Originally posted by dba123

My goal is to use the parameter values incoming in my select statement to bring back data. I am passing in parameters from my ASP.NET app so this stored proc can be very dynamic in that it can select the field names from whatever table I passed to the stored proc's parameters. I know I can't directly do this using parameters but not sure how to form my sql statement so I tried using variables with no luck also.

ALTER PROCEDURE Get_DropDown_ListValues

@TableName varchar(40),
@ValueField varchar(40),
@DisplayField varchar(40)

AS

DECLARE @ValueF varchar(40),
@DisplayF varchar(40),
@TableN varchar(40)

Set @ValueF = @TableName
Set @DisplayF = @ValueField
Set @TableN = @DisplayField

Select @ValueF, @DisplayF FROM @TableN ORDER BY @DisplayF ASC

I get the error: Must declare the variable '@TableN'.

Select @ValueF, @DisplayF FROM @TableN ORDER BY @DisplayF ASC

You can not select the value from the variable which of the varchar type..

you have declare the variable of @Table Data type ..

Something like this

Declare @Tablen Table
(
Valuef Varchar(40),
DisplayF Varchar(40)
)

Insert @TableN
Select @ValueField,@DisplayField

Select * From @TableN

Hope this should work fine.. for you.

Complicated things can be done by simple thinking

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-11-07 : 01:44:07

I don't understand your Insert and 2 selects. Also, I don't understand the parenthesis after your table declaration and why you put the other 2 variables in the parens. Can you walk me through exactly what your code is intending...sorry, I'm new to this.

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-11-07 : 01:46:12

I know this is wrong...

ALTER PROCEDURE Get_DropDown_ListValues

@TableName varchar(40),
@ValueField varchar(40),
@DisplayField varchar(40)

AS

DECLARE @TableN Table
(@ValueF varchar(40), @DisplayF varchar(40))

Set @ValueF = @TableName
Set @DisplayF = @ValueField
Set @TableN = @DisplayField

Select @ValueF, @DisplayF FROM @TableN ORDER BY @DisplayF ASC

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 01:51:10

i just created the table type variable and the parameter which you were passing i inserted into that table type variable..
and then i just did the normal select from the table..

can you post some sample data and expected result you want.. so that you can get the expected result faster.. ???

Complicated things can be done by simple thinking

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-11-07 : 02:00:03

So picture me passing the values "tbl_City", "CityName", and "CityID" to the stored Proc Parameters from ASP.NET

I want to take those values and using the parameters (which now contain the tablename and field names) and create my select statement based off the parameters in my stored procedure.

so essentially what should happen is I should be able to do this:

SELECT @ValueField, @DisplayField FROM @TableName ORDER BY @DisplayField ASC

which by using the parameters would equate to:

SELECT CityID, CityName FROM tbl_City ORDER BY CityName

So in otherwords, I can reuse this stored proc in that I could pass in any table name and field names from my ASP.NET app to produce the select statement and grab the info I need.

Let me know if I'm being clear enough. I don't necessarily need variables if there is some work around to allow my select statement to always use the values behind my parameters...or in other words the incoming values passed to those parameters from my ASP.NET application.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-07 : 02:11:12

oh oks..
for this i guess you have to write the dynamic sql..

Somthing like this may helps you..

Alter PROCEDURE Get_DropDown_ListValues

@TableName varchar(40),
@ValueField varchar(40),
@DisplayField varchar(40)

AS

Declare @Qry Varchar(8000)
Set @Qry = 'Select ' +@ValueField +',' + @DisplayField + ' From ' + @TableName

Exec(@Qry)

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources