CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Ambitioussignal
Ambitioussignal

Reputation: 131

SQL Server Sample Proc Creation

I am not getting the result while executing a stored procedure in SQL Server 2014. I have tested the following script 

DROP proc firstproc

create procedure firstproc
@EmailPromotion int,
@Title nvarchar(100) Out
--@Firstname nvarchar(100) out,
--@MiddleName nvarchar(100) out,
--@LastName nvarchar(100)
as
Begin
select @Title=Title
-- @firstname=Firstname, @middlename=MiddleName,@LastName=LastName
from AdventureWorks2014.person.Person where EmailPromotion=@EmailPromotion
and
@Title is not null
End


--@Firstname nvarchar(100) out,
--@MiddleName nvarchar(100) out,
--@LastName nvarchar(100)

declare @Title1 nvarchar(100)
exec firstproc 2, @Title1 Out
print @Title1

--> No Result

Actual result of the select query:

select Title from
AdventureWorks2014.person.Person where EmailPromotion=2
and
Title is not null

Ms.
Mr.
Sr.
Ms.
Ms.
Mr.
Mr.
Mr.

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

This is the query you are running in the stored procedure:

select @Title = Title
       -- @firstname=Firstname, @middlename=MiddleName,@LastName=LastName
from AdventureWorks2014.person.Person
where EmailPromotion = @EmailPromotion and
      @Title is not null;

The = says to assign the results of the stored procedures to variables. In SQL Server, a stored procedure can either return values or assign to variables, but not both. Hence, the output you want is disappearing into variables that you do not see.

Upvotes: 1

Related Questions