trouble passing column name parameter to sproc

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

SQL Server Development (2000)

trouble passing column name parameter to sproc

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-27 : 12:51:38

Hi,

I'm testing dynamic sql in an SPROC that is accessed from an ASP page. Users will be able to pick columns, tables, and a filter or two from drop down boxes. My SPROC works when I do dynamic SQL using only the TABLENAME variable. However, when I add the column variables and the filter after WHERE, I keep getting "must declare the variable errors" for @ID parameters, and @family filter Here's my code. Any suggestions?:

CREATE Procedure sp_dynamic_test
@TableName varChar(100),
@ID1 varchar(100),
@ID2 varchar(100),
@ID3 varchar(100),
@DX varchar(100),
@family varchar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = ' SELECT FIRSTNAME, LASTNAME, @ID1, @ID2, @ID3, @DX '
SELECT @SQL = @SQL + ' FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE Family = COALESCE((NULLIF(@Family,0)),Family) '

Exec ( @SQL)

Edited by - steelkilt on 02/27/2003 12:52:21

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 13:08:57

How About:

Drop Proc usp_PROC1
GO

CREATE PROC usp_PROC1
@TBName sysname, @ReqColsDelimited varchar(4000)
AS
Declare @strSQL varchar(4000),@y int

SELECT @y = CHARINDEX(',',@ReqColsDelimited,1)-1

Select @strSQL = 'SELECT ' + Substring(@ReqColsDelimited,1,@y)

Select @ReqColsDelimited = Substring(@ReqColsDelimited,@y+2,Len(@ReqColsDelimited)-(@y+1))

While @y > 0
BEGIN
Select @y = CHARINDEX(',',@ReqColsDelimited,1)-1

If @y > 0
BEGIN
Select @strSQL = @strSQL + ', ' + Substring(@ReqColsDelimited,1,@y)
Select @ReqColsDelimited = Substring(@ReqColsDelimited,@y+2,Len(@ReqColsDelimited)-(@y+1))
END
END

Select @strSQL = @strSQL + ', '+RTrim(@ReqColsDelimited)+' FROM ' + @TBName

Select @strSQL

Exec(@strSQL)

GO

Execute usp_PROC1 'sysobjects','name,type'
GO

Hope this helps

Brett

8-)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-27 : 13:24:09

I like X002548 solution,
If you need something quick before having to convert your interface try this

CREATE Procedure sp_dynamic_test
@TableName varChar(100),
@ID1 varchar(100),
@ID2 varchar(100),
@ID3 varchar(100),
@DX varchar(100),
@family varchar(100)
AS

Declare @SQL VarChar(1000)
SELECT @SQL = ' SELECT FIRSTNAME, LASTNAME,' + @ID1 + ',' + @ID2 + ',' + @ID3 + ',' + @DX
SELECT @SQL = @SQL + ' FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE ' + @Family + ' IS NULL OR Family = ' + @Family

Exec ( @SQL)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-27 : 15:35:07

Thanks all.
Valter, any idea why the WHERE section of this SPROC keeps returning "invalid column name JonesFirst" when varchar Family identifier JonesFirst is passed to the SPROC?

Edited by - steelkilt on 02/27/2003 15:35:33

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 15:38:37

It's character data. You need to wrap the value with quotes('').

Try this:

SELECT @SQL = @SQL + ' WHERE ' + '''' + @Family + '''' + ' IS NULL OR Family = ' + '''' + @Family + ''''

Good Luck

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-27 : 16:11:27

Hey, thanks for pointing that out. I've been staring at the same SPROC all day and it didn't even occur to me...

Works nicely now.

Cheers.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-28 : 11:29:09

I'm extending this baby, trying to pack everything into one ASP page/ASP action script/SPROC. Any pointers on how to tell the SPROC to ignore a specific column (don't load it in the query result) when the user does not select anything from the drop-down box. Currently, I'm assigning text value 'NoID' to the "no choice", passing this text value to the SPROC, then NULLIF as below. The error I get is "operation not allowed when object is closed" or some such. Code snippet follows:

SELECT @SQL = ' SELECT LASTNAME, SEX,' + @Area + ',' + @ID1 + ',' + (NULLIF(@ID2, 'NoID')) + ',' + (NULLIF(@ID3,'NoID')) + ',' + (NULLIF(@ID4,'NoID'))

thx

Edited by - steelkilt on 02/28/2003 11:30:08

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-28 : 12:30:21

Now using conditional in SPROC

IF @ID2 = 'NoID' AND @ID3 = 'NoID' AND @ID4 = 'NoID'

which works but is a bit "busy"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources