65.9K
CodeProject is changing. Read more.
Home

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

starIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

1.00/5 (1 vote)

Nov 19, 2010

CPOL
viewsIcon

10300

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