July 22, 2010 at 3:10 am
Hi,
I'm new in using tsql and I'm puzzled.
I'm building a database for a school asignment, but it is the firdt time I do so.
When I use this code I need to more arguments then I would expect.
CREATE Procedure dbo.usp_addaccounts
@accountwachtwoord varchar(25),
@accountactive bit,
@accountchangeonfirstlogon bit,
@rolnaam varchar(25),
@rolid int,
@contactpersoonemail varchar(50),
@contactpersoonid int
AS
BEGIN
SELECT @rolid = RolID
FROM dbo.Rollen
WHERE RolNaam = @rolnaam
SELECT @contactpersoonid = ContactpersoonID
FROM dbo.Contactpersonen
WHERE ContactpersoonEmail = @contactpersoonemail
INSERT INTO dbo.Accounts
(AccountWachtwoord, AccountActive, AccountChangeOnFirstLogon, RolID, ContactpersoonID)
VALUES
(@accountwachtwoord, @accountactive, @accountchangeonfirstlogon, @rolnaam, @contactpersoonemail)
END
GO
The arguments I need to run this stored procedure are:
@accountwachtwoord char(25), @accountactive bit, @accountchangeonfirstlogon bit, @rolnaam varchar(25), @rolid int, @contactpersoonemail varchar(50), @contactpersoonid int
The arguments @rolid int and @contactpersoonid int were unexpected.
The idea was that the FK's RolID and ContactpersoonID were found by the SELECT code.
Can anybody explain me why I have to use those 2 extra arguments? Can the code be rewritten so I don't need those extra argument?
Regards,
Stefan.
July 22, 2010 at 3:19 am
Y do u need to pass so much parameters in stored proc??? U can use like...
CREATE Procedure dbo.usp_addaccounts
@accountwachtwoord varchar(25),
@accountactive bit,
@accountchangeonfirstlogon bit,
@rolnaam varchar(25),
--@rolid int, --Remove from here...
@contactpersoonemail varchar(50),
--@contactpersoonid int --Remove from here...
AS
BEGIN
declare @rolid int, @contactpersoonid int --Add here...
SELECT @rolid = RolID
FROM dbo.Rollen
WHERE RolNaam = @rolnaam
SELECT @contactpersoonid = ContactpersoonID
FROM dbo.Contactpersonen
WHERE ContactpersoonEmail = @contactpersoonemail
INSERT INTO dbo.Accounts
(AccountWachtwoord, AccountActive, AccountChangeOnFirstLogon, RolID, ContactpersoonID)
VALUES
(@accountwachtwoord, @accountactive, @accountchangeonfirstlogon, @rolnaam, @contactpersoonemail)
END
GO
July 22, 2010 at 3:47 am
I tried your suggestion, but it made no difference 🙁
July 22, 2010 at 3:56 am
Without knowing your data schema it's not easy to say, but it looks like two of the parameters you're passing in are to do with a person and role. If these are in related tables it might be possible to do this all in one query, something like:
CREATE Procedure dbo.usp_addaccounts
@accountwachtwoord varchar(25),
@accountactive bit,
@accountchangeonfirstlogon bit,
--@rolnaam varchar(25),
--@rolid int,
@contactpersoonemail varchar(50),
--@contactpersoonid int
AS
BEGIN
--SELECT @rolid = RolID
--FROM dbo.Rollen
--WHERE RolNaam = @rolnaam
--
--SELECT @contactpersoonid = ContactpersoonID
--FROM dbo.Contactpersonen
--WHERE ContactpersoonEmail = @contactpersoonemail
INSERT INTO dbo.Accounts
(AccountWachtwoord, AccountActive, AccountChangeOnFirstLogon, RolID, ContactpersoonID)
SELECT @accountwachtwoord, @accountactive, @accountchangeonfirstlogon, R.RollID, C.ContactpersoonID
FROM dbo.Contactpersonen C
JOIN dbo.Rollen R ON R.<join Field> = C.<join field>
WHERE C.ContactpersoonEmail = @contactpersoonemail
GO
Just a thought.
July 22, 2010 at 4:20 am
Hmm, I'm not sure if this is what I'm looking for. Maybe my intention was not clear.
Where I'm looking for is a way to make a stored procedure for filling a table with 2 FK's in it. So I'm looking for a way to get the FK RolID by using the value the dbo.Rollen Rolname and the FK ContactpersoonID by using the value of dbo.Contactpersonen.ContactPersoonEmail.
Both Rolname and ContactPersoonEmail are NOT NULL values.
July 22, 2010 at 5:20 am
SSC Rookie was right after all. There was only 1 declare statement to many.
After I restarted MS SQL server Management Studio, it was working.
Thanks for the help.
July 22, 2010 at 10:44 am
The code that christopher gave you is probably better to use since you don't have to declare any variables, Just select the values from the table as you are inserting them.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply