help me to solve this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help me to solve this

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-06-25 : 23:51:14

i want to exectue the following dynamic sql statement

declare @stdattribute varchar(50),@paramdefinition nvarchar(500),@l_value varchar(50),@l_sql nvarchar(2000),@l_hiplid varchar(50),@p_hiplid varchar(50),@stdattribute1 varchar(50)
SET @L_SQL='SELECT @L_FINALVALUE=[@STDATTRIBUTE] FROM TBL_STDATTRIB_TEMPLATE WHERE HIPL_ID=@L_HIPLID'
SET @PARAMDEFINITION='@L_HIPLID VARCHAR(50),@STDATTRIBUTE VARCHAR(50),@L_FINALVALUE VARCHAR(50) OUTPUT'
-- SELECT @STDATTRIBUTE=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TBL_STDATTRIB_EXPORT' AND ORDINAL_POSITION=7
set @p_hiplid='hofpr00023'
select @stdattribute1=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TBL_STDATTRIB_EXPORT' AND ORDINAL_POSITION=7
EXECUTE SP_EXECUTESQL @L_SQL,@PARAMDEFINITION,@L_HIPLID=@p_hiplid,@stdattribute=@stdattribute1,@l_finalvalue=@l_value output

It is executing but displaying the output as null even though value is there in the column

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-25 : 23:57:10

as the error message suggest

declare @l_sql nvarchar(2000), @paramdefinition nvarchar(500)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 00:45:03

Please do not amend your original post. For those who are reading this for the 1st time, will be confused.

You can't pass in the column name as a parameter

DECLARE 
	@stdattribute 	varchar(50),
	@paramdefinition nvarchar(500),
	@l_value 	varchar(50),
	@l_sql 		nvarchar(2000),
	@l_hiplid 	varchar(50),
	@p_hiplid 	varchar(50),
	@stdattribute1 	varchar(50)

SELECT 	@stdattribute1 = COLUMN_NAME 
FROM 	INFORMATION_SCHEMA.COLUMNS 
WHERE 	TABLE_NAME		= 'TBL_STDATTRIB_EXPORT' 
AND 	ORDINAL_POSITION	= 7

SELECT @L_SQL	= 'SELECT @L_FINALVALUE = [' + @stdattribute1 + '] FROM TBL_STDATTRIB_TEMPLATE WHERE HIPL_ID=@L_HIPLID'

SET @PARAMDEFINITION	= '@L_HIPLID varchar(50), @L_FINALVALUE varchar(50) OUTPUT'

SET @p_hiplid='hofpr00023'

EXECUTE SP_EXECUTESQL @L_SQL,@PARAMDEFINITION, @L_HIPLID = @p_hiplid, @l_finalvalue = @l_value OUTPUT

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources