Solved

SQL Server Stored Procedure -- Variable will receive query result

Posted on 2011-09-09
6
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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