SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 trouble passing column name parameter to sproc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 02/27/2003 :  12:51:38  Show Profile  Reply with Quote
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 - 02/27/2003 :  13:08:57  Show Profile  Reply with Quote
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-)

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 02/27/2003 :  13:24:09  Show Profile  Reply with Quote
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)





Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 02/27/2003 :  15:35:07  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/27/2003 :  15:38:37  Show Profile  Reply with Quote
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-)


Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 02/27/2003 :  16:11:27  Show Profile  Reply with Quote
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.

Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 02/28/2003 :  11:29:09  Show Profile  Reply with Quote
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
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 02/28/2003 :  12:30:21  Show Profile  Reply with Quote
Now using conditional in SPROC

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

which works but is a bit "busy"



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000