Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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 ?


0
cscg1976
Asked:
cscg1976
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
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
 
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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
 
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:
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now