Dynamic SQL!

Hi there,

I am trying to create a dynamic sql statement as follows:

ALTER PROCEDURE [dbo].[GET_FIS_LONGTITLE]
-- Add the parameters for the stored procedure here
@.TABLENAME VARCHAR(25),
@.COLUMNNAME VARCHAR(25),
@.COLUMNVALUE VARCHAR(25),
@.RETURNVALUE VARCHAR(60) OUT
AS
BEGIN

DECLARE @.SQL AS VARCHAR(4000)

SET @.SQL = 'SELECT LONGTITLE FROM ' + CAST(@.TABLENAME AS VARCHAR(25)) +
' WHERE ' + CAST(@.COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@.COLUMNVALUE AS VARCHAR(25)) + ''''

execute (@.SQL)

--''' + CAST(@.RETURNVALUE AS VARCHAR(60)) + ''' =

END

here I am trying to get the long title of an item based on the tablename, columnname, the column value. So the select returns the long title from the table as required.

But I want to assign that value to the @.RETURNVALUE So I tried:

SET @.SQL = 'SELECT ''' + CAST(@.RETURNVALUE AS VARCHAR(60)) + ''' = LONGTITLE FROM ' + CAST(@.TABLENAME AS VARCHAR(25)) +
' WHERE ' + CAST(@.COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@.COLUMNVALUE AS VARCHAR(25)) + ''''

It does not work. I do not know what is missing here.

Any help would be greatly appreciated.

thanks,

Murthy here

Open up books online, look for sp_Executesql, check the example C " Using the OUTPUT parameter'. Follow the example and modify your code accordingly.

|||

Hi there,

I checked out the books online as you suggested. It is a good exampe but my problem is that even the table name is a variable so I just went to the traditional method without wasting any further time.

thanks anyways,

Murthy here