|
Home > Archive > microsoft.public.sqlserver.server > June 2002 > How to exec a stored procedure that does need to skip the Parameters by placing the commas.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to exec a stored procedure that does need to skip the Parameters by placing the commas.
|
|
| Tin Le 2002-06-22, 8:31 pm |
| How to exec a stored procedure that does need to skip the Parameters by
placing the commas.
Or there is a better way doing this.
Thank you all for help.
Tin Le
Create Procedure usp_Test
@intUserID = 0, --1
strDepartment = 1234 OUTPUT --2
@realSalary real = 0.00, --3
@intAge, --4
@strJobName = 'Developer', --5
@strCounttry = 'USA', --6
strCity = 'Houston' --7
) AS
Example: This one work
Exec usp_Test
intUserID, --1
strDepartment OUPUT --2
Example: This one work too
Exec usp_Test
intUserID, --1
strDepartment OUPUT, --2
fltSalary, --3
1000.90, --4
strJobName --5
Example: This one will not work.
Exec usp_Test
intUserID, --1
strDepartment OUPUT, --2
fltSalary, --3
9999.99, --4
strJobName, --5
, --6 Problem here
strLocation OUPUT --7
Example: This one neither
Exec usp_Test
intUserID, --1
strDepartment OUPUT, --2
fltSalary, --3
, --4 Problem here
strJobName, --5
, --6 Problem here
strLocation OUPUT --7
| |
| Andrew J. Kelly 2002-06-22, 8:31 pm |
| Not sure I understand the question but if your asking how to only send
certain parameters then you can do the following: Make sure all parameters
have a default value and then use the parameter name and value pair in the
exec statement. Like EXEC your_sp @Param1 = 23, @Param7 = 'dhdh'
Take a look at EXECUTE in booksonline for more details.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Tin Le" <let@iconus.com> wrote in message
news:O3$q6bWECHA.2324@tkmsftngp03...
> How to exec a stored procedure that does need to skip the Parameters by
> placing the commas.
>
> Or there is a better way doing this.
>
> Thank you all for help.
>
> Tin Le
>
> Create Procedure usp_Test
> @intUserID = 0, --1
> strDepartment = 1234 OUTPUT --2
> @realSalary real = 0.00, --3
> @intAge, --4
> @strJobName = 'Developer', --5
> @strCounttry = 'USA', --6
> strCity = 'Houston' --7
> ) AS
>
>
> Example: This one work
> Exec usp_Test
> intUserID, --1
> strDepartment OUPUT --2
>
> Example: This one work too
> Exec usp_Test
> intUserID, --1
> strDepartment OUPUT, --2
> fltSalary, --3
> 1000.90, --4
> strJobName --5
>
> Example: This one will not work.
> Exec usp_Test
> intUserID, --1
> strDepartment OUPUT, --2
> fltSalary, --3
> 9999.99, --4
> strJobName, --5
> , --6 Problem here
> strLocation OUPUT --7
>
>
> Example: This one neither
> Exec usp_Test
> intUserID, --1
> strDepartment OUPUT, --2
> fltSalary, --3
> , --4 Problem here
> strJobName, --5
> , --6 Problem here
> strLocation OUPUT --7
>
>
>
| |
| Tin Le 2002-06-22, 8:32 pm |
| Thank you Mike and Thank you to Andrew for your help.
What I want to exec a stored procedure by input some parameters and skip
some parameters.
eg: Use the same sinario and all Paramters have a default values. So when I
Set @intUserID = 0, --1
Set @strDepartment = Null --2
Set @realSalary = 0.00, --3
Set @intAge = 10, --4
Set @strJobName = 'Developer', --5
Set @strCounttry = 'USA', --6
Set strCity = 'Houston' --7
And exec it by
Exec usp_Test @intUserID, @strDepartment , @realSalary -- Works here
Exec usp_Test @intUserID, @strDepartment , , ,@strJobName -- Not
works here. Because commas.
and this is my question of how to do this.
Thanks you all
Sincerely yours,
Tin Le
"Mike: Unless you specify your parameters in the exact order and
sequence defined in your SP you need to refer to the
parameters by name. Eg:
Exec usp_Test
@intUserID =intUserID, --1
@strDepartment=strDepartment OUPUT, --2
@fltSalary=fltSalary, --3
@intAge=9999.99, --4
@strJobName=strJobName, --5
@strLocation=strLocation OUPUT --7
Hope that helps."
"Andrew: Not sure I understand the question but if your asking how to only
send
certain parameters then you can do the following: Make sure all parameters
have a default value and then use the parameter name and value pair in the
exec statement. Like EXEC your_sp @Param1 = 23, @Param7 = 'dhdh'
Take a look at EXECUTE in booksonline for more details.
Andrew J. Kelly SQL MVP
Targitinteractive, Inc."
| |
|
| Tin,
Assuming your stored procedure looks like this...
create proc usp_Test
@UserID int = 0,
@Department varchar(30)= Null
@Salary money = 0.00,
@Age int= 10,
@JobName varchar(30)= 'Developer',
@Counttry varchar(30)= 'USA',
@City varchar(30)= 'Houston'
as
.....
GO
your execute should look like this...
Exec usp_Test @userid=@intUserID, @department=@strDepartment ,
@jobname=@strJobName
--
-oj
Rac v2.1 coming soon
http://www.rac4sql.net
"Tin Le" <let@iconus.com> wrote in message
news:#Rmqo#iECHA.2036@tkmsftngp03...
> Thank you Mike and Thank you to Andrew for your help.
>
> What I want to exec a stored procedure by input some parameters and skip
> some parameters.
> eg: Use the same sinario and all Paramters have a default values. So when
I
> Set @intUserID = 0, --1
> Set @strDepartment = Null --2
> Set @realSalary = 0.00, --3
> Set @intAge = 10, --4
> Set @strJobName = 'Developer', --5
> Set @strCounttry = 'USA', --6
> Set strCity = 'Houston' --7
> And exec it by
> Exec usp_Test @intUserID, @strDepartment , @realSalary -- Works
here
> Exec usp_Test @intUserID, @strDepartment , , ,@strJobName -- Not
> works here. Because commas.
> and this is my question of how to do this.
> Thanks you all
>
> Sincerely yours,
>
> Tin Le
>
> "Mike: Unless you specify your parameters in the exact order and
> sequence defined in your SP you need to refer to the
> parameters by name. Eg:
>
> Exec usp_Test
> @intUserID =intUserID, --1
> @strDepartment=strDepartment OUPUT, --2
> @fltSalary=fltSalary, --3
> @intAge=9999.99, --4
> @strJobName=strJobName, --5
> @strLocation=strLocation OUPUT --7
> Hope that helps."
>
> "Andrew: Not sure I understand the question but if your asking how to only
> send
> certain parameters then you can do the following: Make sure all
parameters
> have a default value and then use the parameter name and value pair in the
> exec statement. Like EXEC your_sp @Param1 = 23, @Param7 = 'dhdh'
>
> Take a look at EXECUTE in booksonline for more details.
> Andrew J. Kelly SQL MVP
> Targitinteractive, Inc."
>
>
| |
| Tin Le 2002-06-22, 8:32 pm |
| Yes, I got it, I mean I do understand how it works now.
Thank you very much OjNgo.
Sincerely yours,
Tin Le
"oj" <nospam_ojngo@home.com> wrote in message
news:#ipS9djECHA.2440@tkmsftngp05...
> Tin,
>
> Assuming your stored procedure looks like this...
>
> create proc usp_Test
> @UserID int = 0,
> @Department varchar(30)= Null
> @Salary money = 0.00,
> @Age int= 10,
> @JobName varchar(30)= 'Developer',
> @Counttry varchar(30)= 'USA',
> @City varchar(30)= 'Houston'
> as
> ....
> GO
>
> your execute should look like this...
>
> Exec usp_Test @userid=@intUserID, @department=@strDepartment ,
> @jobname=@strJobName
>
> --
> -oj
> Rac v2.1 coming soon
> http://www.rac4sql.net
>
>
> "Tin Le" <let@iconus.com> wrote in message
> news:#Rmqo#iECHA.2036@tkmsftngp03...
> > Thank you Mike and Thank you to Andrew for your help.
> >
> > What I want to exec a stored procedure by input some parameters and skip
> > some parameters.
> > eg: Use the same sinario and all Paramters have a default values. So
when
> I
> > Set @intUserID = 0, --1
> > Set @strDepartment = Null --2
> > Set @realSalary = 0.00, --3
> > Set @intAge = 10, --4
> > Set @strJobName = 'Developer', --5
> > Set @strCounttry = 'USA', --6
> > Set strCity = 'Houston' --7
> > And exec it by
> > Exec usp_Test @intUserID, @strDepartment , @realSalary -- Works
> here
> > Exec usp_Test @intUserID, @strDepartment , , ,@strJobName -- Not
> > works here. Because commas.
> > and this is my question of how to do this.
> > Thanks you all
> >
> > Sincerely yours,
> >
> > Tin Le
> >
> > "Mike: Unless you specify your parameters in the exact order and
> > sequence defined in your SP you need to refer to the
> > parameters by name. Eg:
> >
> > Exec usp_Test
> > @intUserID =intUserID, --1
> > @strDepartment=strDepartment OUPUT, --2
> > @fltSalary=fltSalary, --3
> > @intAge=9999.99, --4
> > @strJobName=strJobName, --5
> > @strLocation=strLocation OUPUT --7
> > Hope that helps."
> >
> > "Andrew: Not sure I understand the question but if your asking how to
only
> > send
> > certain parameters then you can do the following: Make sure all
> parameters
> > have a default value and then use the parameter name and value pair in
the
> > exec statement. Like EXEC your_sp @Param1 = 23, @Param7 = 'dhdh'
> >
> > Take a look at EXECUTE in booksonline for more details.
> > Andrew J. Kelly SQL MVP
> > Targitinteractive, Inc."
> >
> >
>
>
|
|
|
|
|