Set Multiple values on a Variable

I have the following MSSQL line in my trigger
declare @varID varchar(1000)
set @varID = (SELECT [varID] FROM deleted)

the problem is that the query is returning more than one value and is causing an erro.
So let's say value1,value2 and value3

I want the varID to be equal to "value1,value2,value3"

How can i do that ?


cscg1976Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simple:
DELETE FROM Table1 WHERE varID IN ( SELECT [varID] FROM deleted )
 
or
 
DELETE t1 
 FROM Table1 t1
  JOIN deleted d
    on d.varid = t.varID 

Open in new window

0
 
Patrick MatthewsCommented:
Hello cscg1976,

How are you using @varID?

Regards,

Patrick
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can create a comma separated value using the steps given in the link:

http://blog.namwarrizvi.com/?p=10

Pass the value to @varID
Its done.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
TextReportCommented:
You can use a cursor
Cheers, Andrew
DECLARE @varID varchar(1000)
DECLARE @Text AS VarChar(100)
 
DECLARE Object_Cursor CURSOR FOR
   SELECT [varID] FROM deleted
 
OPEN Object_Cursor
 
FETCH NEXT FROM Object_Cursor INTO @Text
	  
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @varID IS NULL
       SET @varID = @Text
    ELSE
       SET @varID = @VarID + ', ' + @Text
	
    FETCH NEXT FROM Object_Cursor INTO @Text
  END
 
CLOSE Object_Cursor
 
SELECT @varID AS rv
 
DEALLOCATE Object_Cursor

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
usually, you will use a JOIN or EXISTS or IN clause later...

please do NOT use a cursor in your trigger
please try to avoid "programming" solutions if you can do set-based solutions.
0
 
TextReportCommented:
angelIII "please do NOT use a cursor in your trigger" absolutely sorry I went back to the question and forgot about the trigger.
cscg1976, please disregard my posting
Cheers, Andrew
0
 
cscg1976Author Commented:
I'm using it to set an sqlQuery to
set @sqlQuery = 'DELETE FROM Table1 WHERE varID IN (''' + @varID + ''')'
0
 
Patrick MatthewsCommented:
cscg1976,

With respect, you are going about this the wrong way :)

Instead, just run the delete directly:

DELETE Table1
FROM deleted d INNER JOIN
      Table1 t ON d.varID = t.varID

Regards,

Patrick
0
 
Patrick MatthewsCommented:
I need faster fingers, apparently :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.