Passing variables in SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing variables in SP

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2015-02-11 : 19:01:26

I was tasked with witing a stored procedure to send out emails based on certain criteria. One of our developers decided to take care of the criteria in another process and pass variables for me to use. I am not quite sure how to go about this, but below is what I started with. I declared his variables, and then set up a couple of my own for the email subject line and text.

create PROCEDURE [dbo].[SendWelcomeEmails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Is_Admin bit,
@Found bit,
@Email varchar(100),
@Name varchar(100),

AS
DECLARE @subject_msg NVARCHAR(MAX),
@Email NVARCHAR(MAX)

IF(@Is_Admin=0)
SET @subject_msg ='subject line message text here',@Email = 'email message text here'

ELSE if (@Is_Admin=1) SET @subject_msg1 ='subject line message text here' ,@Email = 'email message text here'
************************

Do I need to use a cursor or can I just specify using if, then else? Also not sure where to SET the individual subject lines.

Any help or examples would be greatly appreciated.

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-02-11 : 19:08:26

Not sure if this works for you but...

create PROCEDURE [dbo].[SendWelcomeEmails] 
    @Is_Admin bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


DECLARE @Is_Admin bit,
@Found bit,
@Email varchar(100),
@Name varchar(100),

AS
DECLARE @subject_msg NVARCHAR(MAX),
@Email NVARCHAR(MAX)

IF(@Is_Admin=0) 
SELECT @subject_msg ='subject line message text here',@Email = 'email message text here'

ELSE if (@Is_Admin=1) SELECT @subject_msg1 ='subject line message text here' ,@Email = 'email message text here'

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

dzabor
Posting Yak Master

138 Posts

Posted - 2015-02-12 : 16:38:17

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources