June 5, 2006 at 12:12 pm
Hey, kinda new to the T-SQL scene. Hoping someone can help me figure out whats going on here:
-------------- code --------------------
CREATE Procedure SearchAll
@Target varchar(100),
@method varchar(20),
@media varchar(30),
@genre varchar(20),
@disc varchar(30),
@level varchar(30),
@period varchar(30),
@selection varchar(30),
@sortType varchar(30),
@accept integer
As
.................
Declare @select1 as varchar(500)
Set @select1 = 'Select Reference.RefID, Reference.CreatorString As CreatorString, Genre.Type As Media, dbo.getRowRank(@Target, Reference.RefId) as Rank'
...............
Set @Last = ' Order By Rank ASC, Reference.CreatorString '
Exec(@select1 + @from1 + @where1 + @wherePlus + @Last)
-------------- end code ----------------
The problem here is with the getRowRank function within the select statement. All the code worked fine before I added that line within the select statement, and then tried to order by that column. The function works fine when called in Query Analyzer.
So when I go to Visual Studio and build up the associated page. I try to run the stored procedure from that webpage within an input, and I get a nasty error:
"Must declare the variable @Target" -Clearly its been declared! It's a parameter to the procedure. The rest of the procedure uses this variable many times without error. What's going on?
June 5, 2006 at 12:42 pm
Set @select1 = 'Select Reference.RefID, Reference.CreatorString As CreatorString, Genre.Type As Media, dbo.getRowRank(' + @Target + ', Reference.RefId) as Rank'
I don't think you need dynamic sql to solve this problem though.
Cheers,
* Noel
June 5, 2006 at 12:52 pm
That didn't solve the problem, unfortunately. It did change the error message, though!
Now I get an "Invalid column name: XXXXXX" error where XXXXX is the value which was just entered into the search field. I guess because its outside the quotation, its now trying to handle it as a column?
Any help is appreciated. Thank you!
June 5, 2006 at 1:04 pm
Andrew,
If you got that error is because the Function is expecting a varchar. In that case simply double the quotes like:
Set @select1 = 'Select Reference.RefID, Reference.CreatorString As CreatorString, Genre.Type As Media, dbo.getRowRank(''' + @Target + ''', Reference.RefId) as Rank'
Cheers,
* Noel
June 5, 2006 at 1:06 pm
Dynamic SQL is usually a bad idea, but for a quick fix,
Set @select1 = 'Select Reference.RefID, Reference.CreatorString As CreatorString, Genre.Type As Media, dbo.getRowRank(''' + replace(@Target,'''','''''') + ''', Reference.RefId) as Rank'
will put the appropriate quotes around your string and ensure that embedded quotes are handled. When you specify a string literal, the whole string is surrounded by a pair of single quotes, and any single quotes inside the string have to be replaced with a pair of single quotes so that the parser knows they don't terminate the string but are inside the string. The parser effectively removes the extra quotes when evaluating the string. This process will occur twice over in one of the expressions above, hence you see four single quotes in a row, themselves surrounded by single quotes. These will become two single quotes in the SQL string, then one when the embedded string literal is evaluated (i.e. when you execute the SQL string.)
[edit: this is the same point as the above, giving an additional explanation]
[another edit: the reason for that particular error being raised is roughly that when SQL meets an un-single-quoted string which is not a keyword, it tries to evaluate it as a column name.]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply