"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