Hey Simon

Thanks for your help and advice with the stored procedures. I have changed
the code u gave me a little to do what i needed. The procedure does what i
need ok but i was wondering how to output this data. I have other stored
procedures where i specify that the variable is for output but im not sure
what type of variable to be using (IE im using VarChar at the moment, is
there an array type ?). At the moment i get the grid showing the results of
the query in sql query analyzer but i would like to output it somehow so
that when i am using the stored procedure with my external program i can use
the data from this stored procedure.

Thanks again for all your help


/*
** Determine Entity Group Memberships
*/

CREATE PROCEDURE [dbo].[EntityStartup]

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

AS

DECLARE @MachineLength Char(2) /* Local Machine Name Length */
DECLARE @MachInt Char(1) /* Machine Integer Counter */

SET @MachInt = 1

SELECT @MachineLength = Len(@MachineName)

CREATE TABLE #GrpMem (GrpName varchar(50), AuthID varchar(50))

WHILE @MachInt <= @MachineLength

BEGIN
INSERT INTO #GrpMem (GrpName, AuthID) SELECT * FROM GrpMachines WHERE
MachineGrp LIKE LEFT(@MachineName,@MachInt)
SET @MachInt = @MachInt + 1
END

SELECT * FROM #GrpMem

GO

"Simon Hayes" <sql@hayes.ch> wrote in message
news:3f912b55$1_1@news.bluewin.ch...
>
> "Jarrod Morrison" <jarrodm@ihug.com.au> wrote in message
> news:bmquie$mot$1@lust.ihug.co.nz...
> > Hi all
> >
> > Im relatively new to using stored procedures and im not sure if it is
> > possible to do what I am trying to do so any help here is greatly
> > appreciated. I am using the variable @MachineName which is obviously the
> > local machine name mainly in this procedure. What is loop through from
the
> > first character of the variable to the last and use this data in a
select
> > statement. I have included the code below for what I have tried so far
but
> I
> > get an error that "Error 116: Only one expression can be specified in
the
> > list when the subquery is not introduced with EXISTS". So im not sure
if
> > im going about this the right way or not but any help anyone give is
> greatly
> > appreciated
> >
> > Thanks
> >
> >
> > /*
> > ** Determine Entity Group Memberships
> > */
> >
> > CREATE PROCEDURE [dbo].[sp_EntityStartup]
> >
> > @MachineName VarChar(50),
> > @UserName VarChar(50)
> >
> > AS
> >
> > DECLARE @MachineLength Char(1) /* Local Machine Name Length */
> > DECLARE @MachInt Char(1) /* Machine Integer Counter */
> >
> > SET @MachInt = 1
> >
> > SELECT @MachineLength = Len(@MachineName)
> > DECLARE @Ttp VarChar(20)
> >
> > WHILE @MachInt <= @MachineLength
> >
> > BEGIN
> >
> > SELECT @Ttp = (SELECT * FROM GrpMachines WHERE MachineGrp LIKE
> > LEFT(@MachineName,@MachInt))
> > SELECT @MachInt = @MachInt + 1
> >
> > END
> >
> > GO
> >
> >
>
> The error message is because you have a scalar variable @Ttp, but your
> subquery can return multiple rows and columns, so the result of the
subquery
> cannot be assigned to it. Perhaps you meant to do something like this?
>
> select @Ttp = count(*)
> from dbo.GrpMachines
> where ...
>
> In addition, you don't do anything with @Ttp inside the loop - it's simply
> updated on each pass though - and the procedure doesn't return any output,
> so it's not clear what you're trying to achieve. Finally, it's generally
> best to avoid naming stored procedures as sp_ - that's used for system
> stored procedures. This may be more like what you want, although I'm just
> guessing:
>
>
> CREATE PROCEDURE dbo.GetEntityStartup
> @MachineName VarChar(50)
> AS
> begin
>
> /* Table to hold output */
> create table #output (PartialName varchar(50), Matches int)
>
> /* Loop Counter */
> declare @i int
> set @i = 1
>
> /* Populate working table */
> while @i <= len(@MachineName)
> begin
> insert into #output (PartialName, Matches)
> select left(@MachineName, @i), count(*)
> from GrpMachines
> where MachineGrp like left(@MachineName, @i) + '%'
>
> set @i = @i + 1
> end
>
> /* Return output */
> select PartialName, Matches
> from #output
> order by PartialName
>
> end
>
>
> Simon
>
>