Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

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

1.00/5 (1 vote)
19 Nov 2010CPOL 10.3K  
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)