[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Server Stored Procedure -- Variable will receive query result

Posted on 2011-09-09
6
Medium Priority
?
453 Views
Last Modified: 2012-06-22
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
Comment
Question by:jonsuns7
6 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36515141
The format is:
Select @whatever = column from table where blah
0
 
LVL 12

Assisted Solution

by:jagssidurala
jagssidurala earned 1000 total points
ID: 36515158
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
 
LVL 9

Expert Comment

by:sarabhai
ID: 36516397
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LVL 9

Accepted Solution

by:
sarabhai earned 1000 total points
ID: 36516399
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36519006
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
 

Author Closing Comment

by:jonsuns7
ID: 36526127
Both solutions ultimatly helped me in accomplishing the task needed.
Thanks Again Everybody,
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question