Solved

SQL Server Stored Procedure -- Variable will receive query result

Posted on 2011-09-09
6
387 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 38

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now