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]Connect With a Mentor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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 MatthewsConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.