473,699 Members | 2,900 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored Procedures

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_EntityStartu p]

@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(@MachineNam e)
DECLARE @Ttp VarChar(20)

WHILE @MachInt <= @MachineLength

BEGIN

SELECT @Ttp = (SELECT * FROM GrpMachines WHERE MachineGrp LIKE
LEFT(@MachineNa me,@MachInt))
SELECT @MachInt = @MachInt + 1

END

GO
Jul 20 '05 #1
3 6579

"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bm******** **@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_EntityStartu p]

@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(@MachineNam e)
DECLARE @Ttp VarChar(20)

WHILE @MachInt <= @MachineLength

BEGIN

SELECT @Ttp = (SELECT * FROM GrpMachines WHERE MachineGrp LIKE
LEFT(@MachineNa me,@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.GetEntitySt artup
@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(@MachineNam e)
begin
insert into #output (PartialName, Matches)
select left(@MachineNa me, @i), count(*)
from GrpMachines
where MachineGrp like left(@MachineNa me, @i) + '%'

set @i = @i + 1
end

/* Return output */
select PartialName, Matches
from #output
order by PartialName

end
Simon
Jul 20 '05 #2
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(@MachineNam e)

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(@MachineNa me,@MachInt)
SET @MachInt = @MachInt + 1
END

SELECT * FROM #GrpMem

GO

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:3f******** **@news.bluewin .ch...

"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bm******** **@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_EntityStartu p]

@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(@MachineNam e)
DECLARE @Ttp VarChar(20)

WHILE @MachInt <= @MachineLength

BEGIN

SELECT @Ttp = (SELECT * FROM GrpMachines WHERE MachineGrp LIKE
LEFT(@MachineNa me,@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.GetEntitySt artup
@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(@MachineNam e)
begin
insert into #output (PartialName, Matches)
select left(@MachineNa me, @i), count(*)
from GrpMachines
where MachineGrp like left(@MachineNa me, @i) + '%'

set @i = @i + 1
end

/* Return output */
select PartialName, Matches
from #output
order by PartialName

end
Simon

Jul 20 '05 #3

"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bn******** **@lust.ihug.co .nz...
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(@MachineNam e)

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(@MachineNa me,@MachInt)
SET @MachInt = @MachInt + 1
END

SELECT * FROM #GrpMem

GO


<snip>

That all depends how your application calls the procedure. If you're using
ADO, for example, you'd get the result set (ie the rows you see in QA) into
a recordset object, and do something with it there. Other client libraries
(ODBC, JDBC etc.) will have their own way of handling it.

You can't use a table variable as an output parameter from a stored proc, so
if you need to pass the result set to another stored proc, then you could
look at using a table-valued UDF instead, as discussed here (among other
approaches):

http://www.algonet.se/~sommar/share_data.html
Simon
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
10750
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!!
2
2815
by: scott | last post by:
Hi, Just wondering what sort of problems and advantages people have found using stored procedures. I have an app developed in VB6 & VB.NET and our developers are starting to re-write some of the code in stored procedures (im advocating encryption of them). When deploying an application however stored procedure seem to add another level of complexity to installation. In future we also plan to have an basic ASP app with some of the...
2
9239
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
5
3391
by: Rhino | last post by:
I am trying to determine the behaviour of stored procedures in DB2 V8.2.x in Windows/Unix/Linux and how I can control that behaviour. Some documentation in the manuals is confusing the issue somewhat. First, am I right in understanding that the normal behaviour of a stored procedure, fenced or unfenced, is to only go into memory when it is invoked and to be swapped out of memory when it is not needed any more? Second, am I right in...
5
3475
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored Procedures. I thought stored pricedures were an Oracle/MS SQL Server thing and don't know how they work with Access Jet. I've looked at some of the help on stored procedures in A2003, but really don't understand what's going on. Can someone...
2
3329
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have several stored procedures to Insert and update datas in our SQL database. Some stored procedures are smaller (insert datas in only one table) and some of them are quite big (insert datas in several
0
2644
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how to call them from an ASP.Net page Every modern database system has a stored procedure language. SQL Server is no different and has a relatively sophisticated and easy to use system. This article will not attempt to go into depth in explaining...
45
3398
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
28
72497
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be accessing views from stored procedures?
11
3430
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up the MQT at the time it is bound on the creation of the static SQL. This raises the question on how you stop it or start it using a MQT as there is no option on the bind. What happens when it is rebound? What happens if the plan is made invalid...
0
9199
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9055
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8947
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8902
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6552
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4392
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3076
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2366
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2016
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.