November 1, 2011 at 9:21 am
I am trying to convert a proc which uses dynamic sql (using exec) to use sp_executesql.
Original proc using exec
DECLARE @@sql VARCHAR(1000)
DECLARE @DocumentID INT
DECLARE @LinkColumn Varchar(50)
DECLARE @LinkID INT
DECLARE @SubTypeID INT
SET @DocumentID = 1
SET @LinkColumn = 'dol_tr_transaction_id'
SET @LinkID = 23
SET @SubTypeID = 270
SET @@sql = '
SELECT count(1)
FROM doc_lks INNER JOIN document
ON do_document_id = dol_do_document_id
WHERE do_document_id != ' + CONVERT(VARCHAR, @DocumentID) +
' AND ' + @LinkColumn + '= ' + CONVERT(VARCHAR, @LinkID)+
' AND do_rvs_doc_subtype = ' + CONVERT(VARCHAR, @SubTypeID) +
' AND ISNULL(do_deleted_bit, 0) = 0'
EXEC (@@sql )
-- print @@sql
Returns 1 Row
Modified proc using sp_executesql
DECLARE @@sql NVARCHAR(1000)
DECLARE @DocumentID INT
DECLARE @LinkColumn Varchar(50)
DECLARE @LinkID INT
DECLARE @SubTypeID INT
SET @DocumentID = CONVERT(VARCHAR, @DocumentID)
SET @LinkID = CONVERT(VARCHAR, @LinkID)
SET @SubTypeID = CONVERT(VARCHAR, @SubTypeID)
SET @@sql = '
SELECT count(1)
FROM doc_lks INNER JOIN document
ON do_document_id = dol_do_document_id
WHERE do_document_id != @DocumentID
AND @LinkColumn like @LinkID ---- original @LinkColumn = @LinkID
AND do_rvs_doc_subtype = @SubTypeID
AND ISNULL(do_deleted_bit, 0) = 0'
EXEC sp_executesql @@sql,N'@DocumentID int, @LinkID int, @SubTypeID INT, @TypeID INT,@LinkColumn VARCHAR(50)',@DocumentID=1,@LinkID=23,@SubTypeID=270,@TypeID=967,@LinkColumn='dol_tr_tran_id'
-- print @@sql
Returns 0 Rows
Acutal select statement
SELECT count(1)
FROM doc_lksINNER JOIN document
ON do_document_id = dol_do_document_id
WHERE do_document_id != 1
AND dol_tr_tran_id like 23
AND do_rvs_doc_subtype = 270
AND ISNULL(do_deleted_bit, 0) = 0
Returns 1 row..
Initially the code was using @LinkColumn + '= ' + CONVERT(VARCHAR, @LinkID) which I changed it to
@LinkColumn like CONVERT(VARCHAR, @LinkID) As I was getting error
"Conversion failed when converting varchar value to datatype int" So changed to like operation which should give me same result.
I was able to get through the above error message but the query is returning no rows as opposed to 1 row using actual select statement.
Am I missing anything near @LinkColumn like CONVERT(VARCHAR, @LinkID)??? where I want user to declare which column he wants to search. The actual column dol_tr_tran_id datatype is int but my variable @LinkColumn is varchar.
November 1, 2011 at 10:28 am
sp_executesql behaves like a dynamic stored procedure; you cannot pass an object name as a parameter.
To use sp_executesql, try something like:
SELECT @sql =
'SELECT COUNT(1) '
+ 'FROM doc_lks '
+ 'INNER JOIN document '
+ 'ON do_document_id = dol_do_document_id '
+ 'WHERE do_document_id != @DocumentID '
+ 'AND ' + @LinkColumn + '= @LinkID '
+ 'AND do_rvs_doc_subtype = @SubTypeID '
+ 'AND ISNULL(do_deleted_bit, 0) = 0'
-- check for injection
WHERE EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = @LinkColumn
AND TABLE_NAME IN ('doc_lks', 'document')
)
EXEC sp_executesql @sql
,N'@DocumentID int, @LinkID int, @SubTypeID int'
,@DocumentID
,@LinkID
,@SubTypeID
November 1, 2011 at 10:31 am
Thanks for the reply. It is helpful
November 1, 2011 at 10:32 am
Looks like you have a couple issues going on that need to be cleaned up.
SET @DocumentID = CONVERT(VARCHAR, @DocumentID)
SET @LinkID = CONVERT(VARCHAR, @LinkID)
SET @SubTypeID = CONVERT(VARCHAR, @SubTypeID)
You are converting to varchar but did not specify the size. Do you know the default size off the top of your head? I know I don't remember. You should specify the size.
The next thing you are doing is using like instead of = with no wildcard.
AND @LinkColumn like @LinkID ---- original @LinkColumn = @LinkID
What is the actual value of @@sql before you execute it and what are the values in each parameter? You have something a little off there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply