Search code examples
sqlsql-serverstored-proceduresdynamicdeclare

SQL Server: how to declare and set variable in dynamic procedure

I would like to declare and set a variable as part of a dynamic procedure.

I am new to this so the following is just to indicate what I am trying to achieve. Can someone show me how to write this correctly (just regarding these lines) ?

@searchMain nvarchar(100) = '',
@searchInput nvarchar(256) = ''

AS
BEGIN

SET NOCOUNT ON;

BEGIN   

DECLARE @sql nvarchar(max),
        @searchDate datetime

CASE WHEN @searchMain = 'col1' THEN SET @searchDate = @searchInput ELSE SET @searchDate = '' END

SET @sql = 'SELECT TOP 100
-- ...

Many thanks in advance for any help with this, Mike.

Solution

  • Change this:

    CASE WHEN @searchMain = 'col1' THEN SET @searchDate = @searchInput ELSE SET @searchDate = '' END

    To this:

    SET @searchDate = CASE WHEN @searchMain = 'col1' THEN @searchInput ELSE '' END