[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

SQL Server Stored Procedure -- Variable will receive query result

Hi Everyone,

I am working with a stored procedure and I want the result of a query to be put into a variable. The query results work with a unique column value, so each query will result in only one row.

The problem is that I get an error in the stored procedure under the word select. The error line is under both the words select

Here is the code:

----------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[aa_populate_necessary_tables]
      -- Add the parameters for the stored procedure here
      @App_Ref_ID_SS                          nvarchar(50),
               @App_Contact_ID_SS                    nvarchar(50)
      
AS
BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
            
      SET NOCOUNT ON;
      -- Declare variables to hold uniqueidentifier values
      
      DECLARE @Add_GUID_SS      uniqueidentifier
      
      DECLARE @Contact_GUID_SS  uniqueidentifier
      

Set @Add_GUID_SS =  SELECT add_GUID from dbo.Addresses where App_Ref_ID = @App_Ref_ID_SS
      
Set @Contact_GUID_SS = Select contacts_GUID from dbo.contacts where App_Contact_ID = @App_Contact_ID_SS
      
      
    -- Insert statements for procedure here
      Insert into dbo._ref_link_table  Values(@Add_GUID_SS, @App_Ref_ID_SS, @Contact_GUID_SS,@App_Contact_ID_SS)


End
GO
-----------------------------------------------------------------------------------------------------

I keep getting errors on the lines that have the select statements. It says incorrect syntax near Select.

Thanks for any help.

Jon
0
jonsuns7
Asked:
jonsuns7
2 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
The format is:
Select @whatever = column from table where blah
0
 
jagssiduralaCommented:
change the below code

Set @Add_GUID_SS =  SELECT add_GUID from dbo.Addresses where App_Ref_ID = @App_Ref_ID_SS

to

SELECT @Add_GUID_SS =  add_GUID from dbo.Addresses where App_Ref_ID = @App_Ref_ID_SS
0
 
sarabhaiCommented:
Set @Add_GUID_SS =   ( SELECT add_GUID from dbo.Addresses where App_Ref_ID = @App_Ref_ID_SS )
     
Set @Contact_GUID_SS = ( Select contacts_GUID from dbo.contacts where App_Contact_ID = @App_Contact_ID_SS )
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sarabhaiCommented:
Set @Add_GUID_SS =   ( SELECT top 1 add_GUID from dbo.Addresses where App_Ref_ID = @App_Ref_ID_SS )
     
Set @Contact_GUID_SS = ( Select top 1 contacts_GUID from dbo.contacts where App_Contact_ID = @App_Contact_ID_SS )
0
 
LowfatspreadCommented:
just do it like this...

with any code you want the fewest meaningful statements possible...

you dont need the intermediate variable declarations, have just the one Insert statement...

nb
  you should still add error processing into your code.


CREATE PROCEDURE [dbo].[aa_populate_necessary_tables]
      -- Add the parameters for the stored procedure here
      @App_Ref_ID_SS                          nvarchar(50),
               @App_Contact_ID_SS                    nvarchar(50)
      
AS
BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
            
      SET NOCOUNT ON;
      -- Declare variables to hold uniqueidentifier values
           
    -- Insert statements for procedure here
      Insert into dbo._ref_link_table  
      Select (SELECT add_GUID from dbo.Addresses where App_Ref_ID = @App_Ref_ID_SS)
            ,@App_Ref_ID_SS
            ,(Select contacts_GUID from dbo.contacts where App_Contact_ID = @App_Contact_ID_SS)
            ,@App_Contact_ID_SS 

     RETURN

End
GO

Open in new window

0
 
jonsuns7Author Commented:
Both solutions ultimatly helped me in accomplishing the task needed.
Thanks Again Everybody,
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now