pretty basic code question

Question

Post reply

pretty basic code question

rightfield

SSCrazy

Points: 2418
More actions
April 20, 2008 at 3:31 pm

#185625

Hi, I am learning from an already written stored procedure that starts out like this:
CREATE PROCEDURE [dbo].z_rpt_CenterOutput
(
@Center varchar(500) = '',
@MGR varchar(500) = '',
@Method varchar(500) = ''
)
AS
BEGIN
SET NOCOUNT ON
-- !! Your SELECT statement goes here !! --
IF @Center = '' OR @Center IS NULL
SET @Center = NULL
IF @MGR = '' OR @MGR IS NULL
SET @MGR = NULL
IF @Method = '' OR @Method IS NULL
SET @Method = NULL
SELECT
@Center Center,
@MGR MGR,
@Method Method
SELECT blah
FROM blah
WHERE
(Center=@Center OR @Center IS NULL)
AND(MGR = @MGR OR UAL.UserID = @MGR OR @MGR IS NULL)
AND(Method=@Method OR @Method IS NULL)
My question is what is the first SELECT statement doing? Is it assigning the text 'Center' to the variable @Center and etc. for @MGR, @Team, and @Method? @Center and the other variables are not referenced anywhere else. It looks so basic but I don't even have output to look at to help me. Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Is it assigning the text 'Center' to the variable @Center and etc.

No. That would have to be...

SELECT @Center = 'Center', etc

It's not assigning anything - it's just selecting the value of @Center with a column heading of Center, etc

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Wow... I wonder what the real intent of the code is...

The following part of the code is poorly written...

[font="Courier New"]IF @Center = '' OR @Center IS NULL

SET @Center = NULL

IF @MGR = '' OR @MGR IS NULL

SET @MGR = NULL

IF @Method = '' OR @Method IS NULL

SET @Method = NULL[/font]

... and could be replaced by some very simple code like this...

[font="Courier New"] SELECT @Center = NULLIF(@Center,''),

@MGR = NULLIF(@MGR,''),

@Method = NULLIF(@C@Methodenter,'')[/font]

The intent of that code is simply to treat blanks and nulls the same.

The "first" select you're talking about does nothing more than return the values of the variables to either the screen or to the GUI as a single result set. I suspect this is for verification purposes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rightfield SSCrazy Points: 2418 More actions · Answer 3

Thanks Ryan and Jeff, without the output from the procedure I guess I just couldn't wrap my head around the concept that a working procedure was doing something kind of goofy.

I thought I knew how to write a proper assignment but I proposed that it was assigning values to the variables because I could more easily imagine myself having to learn new shortcuts than I could imagine that apparently someone was just sloppy and left junk in the procedure.

I will have to mark this procedure for later investigation to see what the first SELECT is really doing.

Thanks again,

Warm regards,

rightfield SSCrazy Points: 2418 More actions · Answer 4

Jeff, as far as the code possibly having been used for verification I was lead to understand that the procedure sent output to a report and that the report only has a single input. If the code turns out to write an output as well as the main SELECT then I will have to ask the application developer again to explain the interaction of query to report. Thanks for the extra information, it's food for thought.

Warm regards,

J-440512 SSCertifiable Points: 6285 More actions · Answer 5

Your first SELECT simply echoes back to the console the values of the parameters you supplied in the call to the stored procedure.

declare @cmd varchar(2)

set @cmd = 'Hi'

select @cmd cmd or select @cmd AS cmd, just provide a column header.

results in

[font="Courier New"]cmd

----

Hi[/font]