Stored Procedure Syntax

[Jarrod Morrison]

Hi All

Im using a stored procedure on my sql server and am unsure of the syntax
that i should use in it. Im pretty sure that there is a way to do what i
want, but as yet i havent been able to find much info on it. Basically the
procedure takes the machinename and username supplied and searches a table
or two for some matches and this part works great. The only problem i have
is that with the app that ties in with the procedure returns some strange
errors when no matches are found IE a blank recordset is returned. I know
that i can change the program to trap this error but i would prefer to be
able to basically say in the stored procedure, if the result of the SELECT
statement returns no records i want to set the output to be something
instead of nothing if possible. At the moment when i match is found it will
be a 3 digit number IE 001 002 003 etc and i would like to basically say
that if nothing is found make the output to be 000 if possible. Any help is
greatly appreciated

Thanks In Advance


CODE:

CREATE PROCEDURE [dbo].[Memberships]

@MachineName VarChar(50),
@UserName VarChar(50)

AS

DECLARE @MachineLength Int /* Local Machine Name Length */
DECLARE @SrchInt Int /* Search Loop Integer Counter */
DECLARE @SqlStr VarChar(500) /* SQL Select String */
DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */

SET @SrchInt = 1

SET @MachineLength = Len(@MachineNam e)
SET @SqlStr = 'SELECT LocationID FROM Locations WHERE GroupID = '''

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach =LEFT(@MachineN ame,@SrchInt)
IF @SrchInt = 1

BEGIN
SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1) + ''''
END

IF @SrchInt > 1

BEGIN
SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach + ''''
END

SET @SrchInt = @SrchInt + 1

END

SET @SqlStr = @SqlStr + 'UNION SELECT LocationID FROM CustLocations WHERE
MachineName = ' + '''' + @MachineName + ''''

EXEC (@SqlStr)

GO

[Bruce Loving]

Re: Stored Procedure Syntax

simple add after the select

if @@rowcount = 0
select '000'



On Thu, 13 May 2004 20:34:37 +1000, "Jarrod Morrison"
<jarrodm@ihug.c om.au> wrote:
[color=blue]
>Hi All
>
>Im using a stored procedure on my sql server and am unsure of the syntax
>that i should use in it. Im pretty sure that there is a way to do what i
>want, but as yet i havent been able to find much info on it. Basically the
>procedure takes the machinename and username supplied and searches a table
>or two for some matches and this part works great. The only problem i have
>is that with the app that ties in with the procedure returns some strange
>errors when no matches are found IE a blank recordset is returned. I know
>that i can change the program to trap this error but i would prefer to be
>able to basically say in the stored procedure, if the result of the SELECT
>statement returns no records i want to set the output to be something
>instead of nothing if possible. At the moment when i match is found it will
>be a 3 digit number IE 001 002 003 etc and i would like to basically say
>that if nothing is found make the output to be 000 if possible. Any help is
>greatly appreciated
>
>Thanks In Advance
>
>
>CODE:
>
>CREATE PROCEDURE [dbo].[Memberships]
>
> @MachineName VarChar(50),
> @UserName VarChar(50)
>
>AS
>
> DECLARE @MachineLength Int /* Local Machine Name Length */
> DECLARE @SrchInt Int /* Search Loop Integer Counter */
> DECLARE @SqlStr VarChar(500) /* SQL Select String */
> DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */
>
> SET @SrchInt = 1
>
> SET @MachineLength = Len(@MachineNam e)
> SET @SqlStr = 'SELECT LocationID FROM Locations WHERE GroupID = '''
>
> WHILE @SrchInt <= @MachineLength
>
> BEGIN
>
> SET @CurrMach =LEFT(@MachineN ame,@SrchInt)
> IF @SrchInt = 1
>
> BEGIN
> SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1) + ''''
> END
>
> IF @SrchInt > 1
>
> BEGIN
> SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach + ''''
> END
>
> SET @SrchInt = @SrchInt + 1
>
> END
>
> SET @SqlStr = @SqlStr + 'UNION SELECT LocationID FROM CustLocations WHERE
>MachineName = ' + '''' + @MachineName + ''''
>
> EXEC (@SqlStr)
>
>GO
>[/color]

[Jarrod Morrison]

Re: Stored Procedure Syntax

Hi Bruce

Thanks for the reply, this sort of does what i wanted, it does return the
000 but it returns it as a second recordset, so i get a blank recordset and
then another one with the 000. The vb program which interfaces with this
procedure returns an error as before because it doesnt see any data as being
returned and returns a BOF or EOF error. If i run the procedure in query
analyzer it returns

LocationID
-Blank-
(No Column Name)
000

Is there a way to make the 000 appear under LocationID ?

Thanks again

"Bruce Loving" <BRUCE@LOVINGSC ENTS.COM> wrote in message
news:8r4aa01qed k09ffkirmjulhke hhit9b3cu@4ax.c om...[color=blue]
> simple add after the select
>
> if @@rowcount = 0
> select '000'
>
>
>
> On Thu, 13 May 2004 20:34:37 +1000, "Jarrod Morrison"
> <jarrodm@ihug.c om.au> wrote:
>[color=green]
> >Hi All
> >
> >Im using a stored procedure on my sql server and am unsure of the syntax
> >that i should use in it. Im pretty sure that there is a way to do what i
> >want, but as yet i havent been able to find much info on it. Basically[/color][/color]
the[color=blue][color=green]
> >procedure takes the machinename and username supplied and searches a[/color][/color]
table[color=blue][color=green]
> >or two for some matches and this part works great. The only problem i[/color][/color]
have[color=blue][color=green]
> >is that with the app that ties in with the procedure returns some strange
> >errors when no matches are found IE a blank recordset is returned. I know
> >that i can change the program to trap this error but i would prefer to be
> >able to basically say in the stored procedure, if the result of the[/color][/color]
SELECT[color=blue][color=green]
> >statement returns no records i want to set the output to be something
> >instead of nothing if possible. At the moment when i match is found it[/color][/color]
will[color=blue][color=green]
> >be a 3 digit number IE 001 002 003 etc and i would like to basically say
> >that if nothing is found make the output to be 000 if possible. Any help[/color][/color]
is[color=blue][color=green]
> >greatly appreciated
> >
> >Thanks In Advance
> >
> >
> >CODE:
> >
> >CREATE PROCEDURE [dbo].[Memberships]
> >
> > @MachineName VarChar(50),
> > @UserName VarChar(50)
> >
> >AS
> >
> > DECLARE @MachineLength Int /* Local Machine Name Length */
> > DECLARE @SrchInt Int /* Search Loop Integer Counter */
> > DECLARE @SqlStr VarChar(500) /* SQL Select String */
> > DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */
> >
> > SET @SrchInt = 1
> >
> > SET @MachineLength = Len(@MachineNam e)
> > SET @SqlStr = 'SELECT LocationID FROM Locations WHERE GroupID = '''
> >
> > WHILE @SrchInt <= @MachineLength
> >
> > BEGIN
> >
> > SET @CurrMach =LEFT(@MachineN ame,@SrchInt)
> > IF @SrchInt = 1
> >
> > BEGIN
> > SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1) + ''''
> > END
> >
> > IF @SrchInt > 1
> >
> > BEGIN
> > SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach + ''''
> > END
> >
> > SET @SrchInt = @SrchInt + 1
> >
> > END
> >
> > SET @SqlStr = @SqlStr + 'UNION SELECT LocationID FROM CustLocations[/color][/color]
WHERE[color=blue][color=green]
> >MachineName = ' + '''' + @MachineName + ''''
> >
> > EXEC (@SqlStr)
> >
> >GO
> >[/color]
>[/color]

[Joe Celko]

Re: Stored Procedure Syntax

If you'd post DDL and some specs, you can almost certainly write this as
one compiled SELECT statement, instead of this pile of procedural code
and dynamic SQL.

I hope that the VARCHAR(50) datatypes are the results of actual research
and planning. A lot of ACCESS users and newbies use that automatically
and screw up their data integrity. Since @user_name is never used, why
is it a parameter?

Writing parsing loops like this is a really bad coding technique. You
can find a lots of kludges (including one of mine -- Hey, I can write
crappy code, too!) for this in the FAQ for the newsgroup. But the real
answer should look something like this:

SELECT location_id
FROM Locations
WHERE group_id
IN (SELECT machine_name FROM MachineList)
UNION ALL
SELECT location_id
FROM Custlocations
WHERE machine_name
IN (SELECT machine_name FROM MachineList);

I am not sure why Locations and CustLocations are logically different,
but I will assume that they are. Likewise, what is the logical
difference in a group_id and a machine_name? If machines are grouped in
some way, then there might be data design problems.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Bell]

Re: Stored Procedure Syntax

Hi

Using Joe's SQL then I think you require something like:

SELECT location_id
FROM Locations
WHERE group_id
IN (SELECT machine_name FROM MachineList)
UNION ALL
SELECT location_id
FROM Custlocations
WHERE machine_name
IN (SELECT machine_name FROM MachineList)
UNION ALL
SELECT '000'
FROM MachineList
WHERE machine_name
NOT IN (SELECT machine_name FROM Custlocations
UNION ALL
SELECT group_id FROM Locations )


or maybe:


SELECT ISNULL(l.locati on_id , '000' )
FROM MachineList m LEFT JOIN
( SELECT location_id, machine_name
FROM Custlocations
UNION ALL
SELECT location_id, group_id
FROM Locations ) l ON l.machine_name = m.machine_name


John

"Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
news:c8512o$fcm $1@lust.ihug.co .nz...[color=blue]
> Hi Bruce
>
> Thanks for the reply, this sort of does what i wanted, it does return the
> 000 but it returns it as a second recordset, so i get a blank recordset[/color]
and[color=blue]
> then another one with the 000. The vb program which interfaces with this
> procedure returns an error as before because it doesnt see any data as[/color]
being[color=blue]
> returned and returns a BOF or EOF error. If i run the procedure in query
> analyzer it returns
>
> LocationID
> -Blank-
> (No Column Name)
> 000
>
> Is there a way to make the 000 appear under LocationID ?
>
> Thanks again
>
> "Bruce Loving" <BRUCE@LOVINGSC ENTS.COM> wrote in message
> news:8r4aa01qed k09ffkirmjulhke hhit9b3cu@4ax.c om...[color=green]
> > simple add after the select
> >
> > if @@rowcount = 0
> > select '000'
> >
> >
> >
> > On Thu, 13 May 2004 20:34:37 +1000, "Jarrod Morrison"
> > <jarrodm@ihug.c om.au> wrote:
> >[color=darkred]
> > >Hi All
> > >
> > >Im using a stored procedure on my sql server and am unsure of the[/color][/color][/color]
syntax[color=blue][color=green][color=darkred]
> > >that i should use in it. Im pretty sure that there is a way to do what[/color][/color][/color]
i[color=blue][color=green][color=darkred]
> > >want, but as yet i havent been able to find much info on it. Basically[/color][/color]
> the[color=green][color=darkred]
> > >procedure takes the machinename and username supplied and searches a[/color][/color]
> table[color=green][color=darkred]
> > >or two for some matches and this part works great. The only problem i[/color][/color]
> have[color=green][color=darkred]
> > >is that with the app that ties in with the procedure returns some[/color][/color][/color]
strange[color=blue][color=green][color=darkred]
> > >errors when no matches are found IE a blank recordset is returned. I[/color][/color][/color]
know[color=blue][color=green][color=darkred]
> > >that i can change the program to trap this error but i would prefer to[/color][/color][/color]
be[color=blue][color=green][color=darkred]
> > >able to basically say in the stored procedure, if the result of the[/color][/color]
> SELECT[color=green][color=darkred]
> > >statement returns no records i want to set the output to be something
> > >instead of nothing if possible. At the moment when i match is found it[/color][/color]
> will[color=green][color=darkred]
> > >be a 3 digit number IE 001 002 003 etc and i would like to basically[/color][/color][/color]
say[color=blue][color=green][color=darkred]
> > >that if nothing is found make the output to be 000 if possible. Any[/color][/color][/color]
help[color=blue]
> is[color=green][color=darkred]
> > >greatly appreciated
> > >
> > >Thanks In Advance
> > >
> > >
> > >CODE:
> > >
> > >CREATE PROCEDURE [dbo].[Memberships]
> > >
> > > @MachineName VarChar(50),
> > > @UserName VarChar(50)
> > >
> > >AS
> > >
> > > DECLARE @MachineLength Int /* Local Machine Name Length */
> > > DECLARE @SrchInt Int /* Search Loop Integer Counter */
> > > DECLARE @SqlStr VarChar(500) /* SQL Select String */
> > > DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */
> > >
> > > SET @SrchInt = 1
> > >
> > > SET @MachineLength = Len(@MachineNam e)
> > > SET @SqlStr = 'SELECT LocationID FROM Locations WHERE GroupID = '''
> > >
> > > WHILE @SrchInt <= @MachineLength
> > >
> > > BEGIN
> > >
> > > SET @CurrMach =LEFT(@MachineN ame,@SrchInt)
> > > IF @SrchInt = 1
> > >
> > > BEGIN
> > > SET @SqlStr = @SqlStr + LEFT(@MachineNa me,1) + ''''
> > > END
> > >
> > > IF @SrchInt > 1
> > >
> > > BEGIN
> > > SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach + ''''
> > > END
> > >
> > > SET @SrchInt = @SrchInt + 1
> > >
> > > END
> > >
> > > SET @SqlStr = @SqlStr + 'UNION SELECT LocationID FROM CustLocations[/color][/color]
> WHERE[color=green][color=darkred]
> > >MachineName = ' + '''' + @MachineName + ''''
> > >
> > > EXEC (@SqlStr)
> > >
> > >GO
> > >[/color]
> >[/color]
>
>[/color]