Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
return a variable

Answered return a variable

  • Monday, December 31, 2012 6:54 PM
     
      Has Code
    Sign In to Vote
    0

    I am trying to create a select that includes a contacts full name in the result set.  First name, last name, etc., are stored in the members table. The organization table contains a memberid column that corresponds to members.memberid. There is a stored PRocedure that takes  the memberid as an arguement, concatenates the members name into a single string and returns it as the full name. I thought that I could call that sp and include the variable in the result  set - along with the organization info. My attempt does not work. Is there a way to do this? Executing the GetRiderFullName sp does return the name properly, but I guess you can't use the @fullname in the result set.

    ALTER PROCEDURE dbo.getOrgContact
@memberid int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
DECLARE @fullname nvarchar(100)
EXECUTE GetRiderFullName @memberid
    -- Insert statements for procedure here
    
	SELECT 
	 organizerid 
	,orgname
	,@fullname
	FROM organizer o
	JOIN members m
	ON   m.memberid = o.contactid
	WHERE m.memberid =  @memberid
END
GO

     

All Replies

  • Monday, December 31, 2012 7:14 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Hi

    problem happen due to OUTPUT is not written there :

    Declare calling SP as below :

    CREATEPROC GetRiderFullName     @memberid int, @name nvarchar(100) OUTPUT 
AS

SELECT @name=First+Middle+last


WHERE membered=@memberid

    Execute it :

    DECLARE @fullname nvarchar(100)

EXEC GetRiderFullName     @memberid,  @name =@fullname OUTPUT

    I don't compile it but hope it will work :)
     

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/




     
  • Monday, December 31, 2012 7:15 PM
     
     Proposed
    Sign In to Vote
    1

    The first thing to do is be conscious of what the stored procedure is actually producing and how that affects the consumption of the result.  I'll guess that this stored procedure generates a resultset.  If so, you cannot access that resultset in the manner you are attempting.  Before offering suggestions, there are some basic things to consider. 

    First, always ALWAYS indicate which version/sp of sql server you are using.  If your database is currently using a compatibility setting for a prior version, tell us that as well. 

    Second, are you stuck with the structure and implementation as is, or can you change it?  I ask because it seems that a stored procedure is the wrong implementation to simply concatenate columns together.  There are better alternatives.  A computed column for the table is the most obvious one - but perhaps there is more specialized logic than is apparent in your description. 

    Lastly, please have a look at the sticky posts at the top of the forum.  Though this issue may seem trivial to you now, it may be that there are other aspects of this that need to be explored.  Namely, you seem to be assuming a 1:1 relationship between the 2 tables.  That seems perfectly safe, but posting DDL will allow that to be verified. 

    One final comment.  Logically you do not actually need to join the tables.  Given that member information is effectively static, you only need to select from the organizer table - of course, once you have the full name of the selected member.  How you do that depends on the logic of the stored procedure, the options available to you to implement this differently, the table(s) containing the name of the member, etc.

     
  • Monday, December 31, 2012 7:39 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Looks like you don't need to call another procedure and can do instead:

    ALTER PROCEDURE dbo.getOrgContact
@memberid int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    
	SELECT 
	o. organizerid 
	,o.orgname
	,coalesce(m.FirstName + ' ','') + coalesce(m.LastName,'') as FullName
	FROM organizer o
	JOIN members m
	ON   m.memberid = o.contactid
	WHERE m.memberid =  @memberid
END
GO

    I assumed that Members table has columns FirstName and LastName and this is what you wanted to return as the full name. If not, can you please post the GetRiderFullName stored procedure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, December 31, 2012 7:49 PM
     
     Proposed Has Code
    Sign In to Vote
    0

    Hi Dave Parker

    This seems to work:

    IF OBJECT_ID('dbo.GetRiderFullName') IS NOT NULL DROP PROCEDURE dbo.GetRiderFullName
GO
CREATE PROCEDURE dbo.GetRiderFullName
@memberid int
,@fullname varchar(max) OUTPUT
AS
BEGIN
SET @fullname = 'Tim Hortons'
END
GO

IF OBJECT_ID('dbo.getOrgContact') IS NOT NULL DROP PROCEDURE dbo.getOrgContact
GO
CREATE PROCEDURE dbo.getOrgContact
 @memberid int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
DECLARE @ReturnValue int, @fullname nvarchar(100)
EXEC @ReturnValue=GetRiderFullName @memberid,@fullname OUTPUT

DECLARE @AnyNumberOfJoinedTables table (organizerid int,orgname varchar(max),memberid int)
INSERT INTO @AnyNumberOfJoinedTables (organizerid,orgname,memberid) values
(1,'Bobs Palace',1)

SELECT  organizerid 
		,orgname
		,@fullname as FullName from @AnyNumberOfJoinedTables where memberid = @memberid

END
GO

execute dbo.getOrgContact @memberid = 1

    Pérez

    • Proposed As Answer by David Gutter Wednesday, January 02, 2013 3:50 PM
    •  
     
  • Monday, December 31, 2012 9:17 PM
     
     
    Sign In to Vote
    0

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. For example, do you really know anyone that a full name that 100 characters long? In Unicode Chinese? The USPS used a mere 35 for a name on a letter. Invite garbage data and you will get it. 

    Basic principle of data modeling; a data element has one and only one name. Apparently an organizer is also a member, not a totally different kind of entity as you have shown! 

    What we need to know is what event makes a member play the role of an organizer. 

    Think in sets; the set you are dealing with is the Membership – an abstract, completed set that exists with zero or more elements in it. Not Members!  A “forest” is not “trees”; “Personnel” is not “employees”; remember this mathematical concept from your Freshman math class on Set Theory? 

    >> There is a stored Procedure that takes the member_id as an argument, concatenates the Membership name into a single string and returns it as the full name. <<

    That would be screwed up! Extra overhead, messes up the optimizer, etc. Your data model is wrong and will simply get worse. You are trying to do display formatting in the database, in total violation of a tiered architecture.  After skipping the set theory class you missed, you also skipped the Software Engineering course. 

    Try this skeleton schema:

    CREATE TABLE Membership
    (member_id INTEGER NOT NULL PRIMARY KEY, -- integer is a bad design
     first_name VARCHAR(20) NOT NULL,
     last_name VARCHAR(20) NOT NULL,
     ..);

    Let's make things more fun :) Let's organize orgies! 

    CREATE TABLE Orgies
    (orgy_id INTEGER NOT NULL PRIMARY KEY,
     orgy_date DATE NOT NULL,
     organizer_member_id INTEGER NOT NULL
       REFERENCES Membership(member_id)
      ON UPDATE CASCADE,
     bartender_member_id INTEGER NOT NULL
       REFERENCES Membership(member_id)
      ON UPDATE CASCADE,
     ..);

    The prefix “organizer” is called a role and it tells us how the data element is used in this table. It does not change the nature of a member (automobiles do not become squids).

    You are still writing Algol/Pascal family code in your head! You expect to have some “pass by value/pass by name/pass by result” procedure model in T-SQL. We do not. This is a one-pass, dumb_as_dirt programming language. It passes simple arguments to the parameters of a Proc or Function. It cannot generate thunks in one pass (from the compiler class?). 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL