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

Question

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.

your question looks like incomplete.Can you try to put the correct statements that you have and tell us what you are trying to achieve.? — classic_vmk, Jun 25 '14 at 19:59
I am assuming that the CASE line is not correct so I would like to know how to write this correctly — Mike, Jun 25 '14 at 20:04

score 1 · Accepted Answer · answered Jun 25 '14 at 20:18

1

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

answered Jun 25 '14 at 20:18

Kevin Suchlicki

3,096
2
15
17

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

1 Answers1