Solved

SQL Server Stored Procedure -- Variable will receive query result

Posted on 2011-09-09
6
430 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 250 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 9

Accepted Solution

by:
sarabhai earned 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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