Declare and Set statements int SP

Category: sql server notification

Question

Jody Petroni on Mon, 03 Feb 2014 00:30:55


Hi All

I have a SP that I am trying to declare and set a variable but it seems that it violates the rules somehow. I cant seem to find any information on using variables and setting them in a Notification statement..

DECLARE

@variable [BIGINT]

SET

@variable =(SELECT column FROM [dbo].[table])

Thanks


Biztalk Developer

Replies

Latheesh NK on Mon, 03 Feb 2014 01:31:58


I am not sure of your actual issue:

But, the below would help for subquery returns more than  value for assignment issue:

create table test_table([column] int)

Insert into test_table Select 1
Insert into test_table Select 2
Insert into test_table Select 1

DECLARE @variable [BIGINT]

SET @variable =(SELECT Top 1 [column] FROM [dbo].test_table)
Select @variable
Select @variable = [column] FROM [dbo].test_table
Select @variable

Jody Petroni on Mon, 03 Feb 2014 02:28:34


Hi,

I seems that when ever I assign a value to the variable I declare inside the SP it violates the Notification being raised.

I am using BizTalk to receive the notification if that helps

Thanks