| Author |
 Topic  |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 02/27/2003 : 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 - 02/27/2003 : 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
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 02/27/2003 : 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 - 02/27/2003 : 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 - 02/27/2003 : 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 - 02/27/2003 : 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 - 02/28/2003 : 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 - 02/28/2003 : 12:30:21
|
Now using conditional in SPROC
IF @ID2 = 'NoID' AND @ID3 = 'NoID' AND @ID4 = 'NoID'
which works but is a bit "busy"
|
|
|
| |
Topic |
|
|
|
trouble passing column name parameter to sproc
