|
Hi all,
I've often wondered about this and always simply worked my way around the issue but I would be curious to know what the correct approach is.
Say I have a stored proc like so:
CREATE PROCEDURE myStoredProc
@myFirstParam INT = 0,
@mySecondParam INT = 0
AS
BEGIN
END
Now I want to call this proc, let's say from another proc where there is a local variable with the name @myLocalVar. I want to pass the value of that local variable to the second parameter of the stored proc so I call it as follows:
EXEC myStoredProc @mySecondParam = @myLocalVar
So far so good, but what if the name of that local variable was @mySecondParam (the same as the parameter name of the proc)? The following won't work:
EXEC myStoredProc @mySecondParam = @mySecondParam
So do I really have to create a new local variable with a different name so that I can pass it to the proc?
DECLARE @myNewVariable INT;
SET @myNewVariable = @mySecondParam;
EXEC myStoredProc @mySecondParam = @myNewVariable
Or is there a way I can pass a variable to a proc if the variable has the same name as the parameter name of the proc?
PS. I'm deliberately using an example where the first parameter of the proc is ommitted because I'm specifically interested in the scenario where the parameter names of a stored proc has to be specified (i.e. not inferred by their order).
|
|
|
|
|
This works for me, local and procedure variable is the same.
DECLARE
@ContentTypeID INT
SET @ContentTypeID = 4
EXEC dbo.ContentTypeSelect @ContentTypeID = @ContentTypeID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How silly of me. I simply assumed that EXEC MyProc @MyParam = @MyParam wouldn't work. Just tested it and no problem at all. 
Thanks.
|
|
|
|
|
|
You do know that you can just list the input values without specifying the parameter name right?
Thus your example becomes.
EXEC myStoredProc @mySecondParam
|
|
|
|
|
Hi
I created one web form and i need to insert all same record more than 10 times using Mysql query. How to insert same record multiple times in Mysql using single query and not using for loop. If anybody knows, please reply me.
one more thing
in Sybase some people sujjesting to go for GO command like
insert into tablename(columnname) values('abc') go 50 it wil insert 50 records like this is there any chance to write Mysql query to insert 50 records using Go command like that, i tried but it is not working
Thanking you,
modified 16-Jan-12 6:44am.
|
|
|
|
|
Your question doesn't really make any sense. Why don't you want to use a for loop ?
You can always issue 10+ same statements to do your insert, but then what's the point to do it this way ?
No memory stick has been harmed during establishment of this signature.
|
|
|
|
|
in Sybase some people sujjesting to go for GO command like
insert into tablename(columnname) values('abc') go 50 it wil insert 50 records like this is there any chance to write Mysql query to insert 50 records using Go command like that, i tried but it is not working
|
|
|
|
|
with out knowing what you code is like and how you are inserting your data have you considered a union statement?
MySQL Union[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
It sounds like your database is in serious need of normalization.
|
|
|
|
|
MySql Insert documentation[^]
As far as I can see you will need to use a loop of some kind, can I ask why you want to do it with no looping? you could easily do it simply like this in psuedo code
Open Database Connection
For i = 1 to 50
Insert into Datbase table the values
i++
next
Close Database
As others have said you may need to reconsider your database design.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
by using for loop i can but is there any possibility to write a single Mysql query to insert 50 or more records. In Sybase they are using GO command like insert into tablename(columnname) values('abc') go 50 it wil insert 50 records.
|
|
|
|
|
The only way I could see you doing that in MySql is to do something like the following but I believe that it will be unmanageable and to be honest I personally only use this approach when I am trying to knock up some dirty data for R&D or testing never used it in a live system
insert into numbers (MyNumber)
select 1
union
select 2
union
select 3
union
select 4
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
According to the MySQL documentation:
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
|
|
|
|
|
Why would you want to duplicate data this way?
|
|
|
|
|
Not sure if this is the right forum for this, but...
In VS2010, when I doule click a .sql file or try to deploy, I get "Object reference not set to an insgtanceof an object". The message appears a number of times one right after another. Sometimes a tab for the file opens with nothing in it. In all cases VS hangs.
This doesn't happen on my work PC, just my home PC.
Anyone know what's causing this? A Google search found this[^] but it's not the problem. I created the SQL DB project in VS as well as the .sql file.
Everything makes sense in someone's mind
|
|
|
|
|
Hi all,
i was trying to do a mutiple select with following fileds
ServiceCategoryName(field)->ServiceCategory(Table)
ServiceName(field)->Service(Table)
ServiceLocation(field)->Location(Table)
Created Date,SendersName,SendersEmaild(fields) ->ServiceItemForward (Table)
DB:SQLServer 2008
and the sp i wrote is following
what changes should i make to this
ALTER PROC [dbo].[usp__Search_ServiceItemForward]
@CategoryID int,
@ServiceID int,
@LocationName varchar(100),
@createdFromDate date,
@createdToDate date,
@sender varchar(100),
@senderEmail varchar(100)
AS
if(@createdFromDate is null)
set @createdFromDate =dateadd(d,-7, CONVERT(date, GETDATE()))
if(@createdToDate is null)
set @createdToDate=CONVERT(date, GETDATE())
Begin
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
SELECT dbo.ServiceItemForward.* FROM dbo.ServiceItemForward INNER JOIN
dbo.ServiceItems ON dbo.ServiceItemForward.ServiceItemID = dbo.ServiceItems.ServiceItemID INNER JOIN
dbo.Service ON dbo.ServiceItems.ServiceID = dbo.Service.ServiceID INNER JOIN
dbo.ServiceCategory ON dbo.Service.ServiceCategoryID = dbo.ServiceCategory.ServiceCategoryID
where
(@CategoryID=0 or ServiceCategory.ServiceCategoryID = @CategoryID)
and (@ServiceID=0 or Service.ServiceID = @ServiceID)
and (@LocationName=0 or ServiceItems.ServiceLocation = @LocationName)
and (ServiceItemForward.CreatedDate Between @createdFromDate and @createdToDate)
and(@sender is null or ServiceItemForward.SendersName=@sender)
and (@senderEmail is null or ServiceItemForward.SendersEmaild=@senderEmail)
COMMIT
End
please help me out in htis....
Thanks in advance
modified 13-Jan-12 5:48am.
|
|
|
|
|
You do realise in your null date test you are populating @createdToDate in both tests.
It would help to know what the problem is!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have cooreected that statement and the code was not wrkng.....
|
|
|
|
|
reogeo2008 wrote: I have cooreected that statement and the code was not wrkng....
If you post a problem, then describe what the code is supposed to do and what the error is. All we see is your code and the statement that it ain't workin', but you didn't explain what the desired result would be like or what you're trying to achieve.
Why is there a commit, if you are only selecting records?
Bastard Programmer from Hell 
|
|
|
|
|
Eddy Vluggen wrote: Why is there a commit, if you are only selecting records?
Just to be sure that he is committed to selecting records only. Nothing else! 
|
|
|
|
|
Not sure it belongs in this forum, I hope it does.
I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds.
How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome.
TIA
|
|
|
|
|
 What is the world coming to? Luc asking questions!!!!
I've done similar things in the past, but not using SQL Server.
I don't know what IPC capabilities are in your server or could be easily grafted onto it.
Given these two points, I'd say:
1. Make the server do it. Client to client gets way too messy when you consider clients appearing/disappearing on the fly. At least the server has a reasonably current idea of who's out there.
2. How I did it was to use sockets with the following simple protocol:
client sends requests
at most one outstanding
first request of a session is a logon
server sends responses to requests OR asynchronous notifications
any socket error => forced logoff
(therefore unexpected logon at server => kill old session, start new)
HTH
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Thanks Peter, yes I need to earn some Enquirer points too you know. 
I'm currently considering a UDP multicast, where producing clients just throw their "new critical data" message up in the air, and consuming clients react on it. I was hoping SQL Server itself would have some built-in mechanism, I need to read up on it I guess.
|
|
|
|
|
Sounds like your app is a little less demanding of accuracy. Mine was a multi-terminal raffle selling setup - server in the back room, one or two fixed PCs and some mobiles (J2ME phones with bluetooth-speaking battery-powered printers) wandering around amongst the patrons. Request/response were the obvious ticket sales (or not), logon/off, operator cash management, etc; the notifications were things like raffle closed, new raffle opening (i.e. clients, come and get the details)
I'd be tempted to write it up - there are some interesting design considerations - except that a lot of the code is Hall of Shame material. 
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
