How to Assign Value to Parameters Used in Query which is Stored in a Variable





1.00/5 (1 vote)
DECLARE /* @STRQUERY AND @PARAMDEF SHOULD BE NVARCHAR BECAUSE "EXECUTE sp_executesql" ACCEPTS STATEMENT AND PARAMETER OF TYPE 'ntext/nchar/nvarchar' ONLY OTHERWISE "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'." OR "Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'." WILL BE RAISED*/ @STRQUERY AS NVARCHAR(200), @STRNAME AS VARCHAR(100), @PARAMDEF AS NVARCHAR(100), @STRNAMEVALUE AS VARCHAR(100) BEGIN --FOLLOWING STATEMENT ASSING VALUE SET @STRNAMEVALUE='MANINDER' --FOLLOWING VARIABLE CONTAINS A QUERY WHICH USES A PARAMETER SET @STRQUERY = 'SELECT ''YOUR NAME IS '' + @STRNAME' --FOLLOWING STATEMENT DEFINES THE PARAMETERS USED IN THE QUERY SET @PARAMDEF = '@STRNAME AS VARCHAR(100)' --IN THE FOLLOWING STATEMENT "@STRNAME = @STRNAMEVALUE" STATEMENT ASSIGNS VALUE TO THE PARAMETER --SYNTAX IS EXECUTE sp_executesql @STATEMENT, @PARAMETERS EXECUTE sp_executesql @STRQUERY, @PARAMDEF,@STRNAME = @STRNAMEVALUE; END