Using COALESCE or IS NULL not workin on User-Defined Table Type

Transact-SQL
1

Is it not possible to use the statements below on a User-Defined Table Type in a Stored Procedure? This works fine if I am pulling from a normal table but not from the User-Defined Table Type.

@tblSEARCH [dbo].[TUD_SEARCH_1] READONLY

DECLARE @CRITERIA AS NVARCHAR(4000) = NULL;
DECLARE @SQLWHERE AS NVARCHAR(4000) = NULL

SELECT @CRITERIA = COALESCE(@CRITERIA + ''',''', '''') + [TUD_CVA_Code] FROM @tblSEARCH
SET @SQLWHERE = 'T1.[CVA_CVA_Code] IN (' + @CRITERIA + ''')'

SELECT @CRITERIA = CASE WHEN @CRITERIA IS NULL THEN [TUD_CVA_Code] ELSE @CRITERIA + ''',''' + [TUD_CVA_Code] END FROM @tblSEARCH
SET @SQLWHERE = 'T1.[CVA_CVA_Code] IN (''' + @CRITERIA + ''')'

2

is not valid SQL, what is the real statement you are using?

When you say its not working, would please post what you get and what you expect?

3

@tblSEARCH [dbo].[TUD_SEARCH_1] READONLY

DECLARE @CRITERIA AS NVARCHAR(4000) = NULL;
DECLARE @SQLWHERE AS NVARCHAR(4000) = NULL;
DECLARE @COMMAND AS NVARCHAR(4000) = NULL;

SELECT @CRITERIA = COALESCE(@CRITERIA + ''',''', '''') + [TUD_CVA_Code] FROM @tblSEARCH
SET @SQLWHERE = 'T1.[CVA_CVA_Code] IN (' + @CRITERIA + ''')'

SELECT @CRITERIA = CASE WHEN @CRITERIA IS NULL THEN [TUD_CVA_Code] ELSE @CRITERIA + ''',''' + [TUD_CVA_Code] END FROM @tblSEARCH
SET @SQLWHERE = 'T1.[CVA_CVA_Code] IN (''' + @CRITERIA + ''')'

SET @COMMAND = 'INSERT INTO [#TMP_TABLE] SELECT * FROM TBL_A_TABLE AS T1 WHERE ' + @ SQLWHERE + ' ORDER BY T1.[FLD_Some_Field]'
EXEC(@COMMAND)

SELECT * FROM [#TMP_TABLE] <-----Returns Nothing.

My guess is the Variable, although its' value is comma delimited and quoted is being viewed by the statement as only one value even though it is being executed. EX: ('5C111','5R112',6Z123') Is there a way to create this example from a User-Defined Table type and use it in dynamic SQL?

4

I might be missing the point completely, but whouldn't the following do the job:

select t1.*
  from tbl_a_table as t1
       inner join dbo.tud_search_1 as t2
               on t2.tud_cva_code=t1.cva_cva_code
;
5

is still not valid SQL

6

Yes it would do the same thing but, when you could have 40 plus joins things tend to start slowing down a little. Using IN () seems to be a little faster.

7

This is the declaration of the User-Defined Table Type ([dbo].[TUD_SEARCH_1] ) to the variable (@tblSEARCH). Not quite sure what you mean that this is not valid SQL. Actually, none of the code I posted is "valid" working SQL in the format I posted. Let me clarify the question a little. The only thing I need to know is will the variable @CRITERIA work in the way that it is being used in the examples above? The variable @CRITERIA will contain a string like so '5612','5691','6542' after the SELECT statement has run.

8

If I run this I get:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@tblSEARCH".

Or are you just missing the DECLARE statement?

It helps us all if you post valid working SQL.[quote="dbay, post:7, topic:8776"]
will the variable @CRITERIA work in the way that it is being used in the examples above
[/quote]

It might...or it might not. This kind of variable use is undefined in SQL Server, though it often works. FOR XML PATH('') is the supported way to do this kind of thing.

see: http://www.sqlshack.com/string-concatenation-done-right-part-1-dubious-practices/

and: https://support.microsoft.com/en-us/kb/287515

for more details

9

Hi gbritton,
@tblSEARCH [dbo].[TUD_SEARCH_1] READONLY - will not work for you unless you have created a User-Defned Table with the name [dbo].[TUD_SEARCH_1]. Once you have created the User-Defined Table, you can add that as a variable in your Stored Procedure to be passed a DataTable from the FrontEnd application. Once you have passed the DataTable, @tblSEARCH becomes the Table. Thank you for the links. I am going over them now and will post back if I find a solution.