MSSQL Stored Procedure

[terencevs]

I am busy creating a stored procedure with variables.

Is it possible to set some variables as not required so when I execute the stored proc I dont have to specify each one?

Thanks!

 

[dequadin]

Yes they're called optional parameters
here, here, here, or just search for yourself

 

[dirkieman]

just default the variable in the declaration:
Create proc dbo.sppKoos

@koos1 int = 0,
@koos2 int

AS
.....

@koos1 does not need to be passed every time

 

[terencevs]

Thanks did search the Net could not find anything that is helping me will look and see what you have giving me helps.

 

[dequadin]

Thanks did search the Net could not find anything that is helping me will look and see what you have giving me helps.

From your post I think you're getting confused with variables and parameters, which is maybe why your searches weren't giving the results you wanted.

Any way you'll see from the links I posted, dirkieman's post is what you need to do.

 

[terencevs]

This is what I needed

http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx

Thanks so much dequadin!

This is the important part:

((@Param1 IS NULL) OR (col1 = @Param1)) AND
((@Param2 IS NULL) OR (col2 = @Param2)) AND
((@Param3 IS NULL) OR (col3 = @Param3))

So when I execute I can do something like this:

exec TestProc @Param1 = '111', @Param2 = '222' or
exec TestProc @Param1 = '111' or
exec TestProc @Param2 = '222'

and get different results.

Thanks again!

 

[dequadin]

This is what I needed

http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx

Thanks so much dequadin!

This is the important part:

((@Param1 IS NULL) OR (col1 = @Param1)) AND
((@Param2 IS NULL) OR (col2 = @Param2)) AND
((@Param3 IS NULL) OR (col3 = @Param3))

So when I execute I can do something like this:

exec TestProc @Param1 = '111', @Param2 = '222' or
exec TestProc @Param1 = '111' or
exec TestProc @Param2 = '222'

and get different results.

Thanks again!

No problem

 

[FarligOpptreden]

This is what I needed

http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx

Thanks so much dequadin!

This is the important part:

((@Param1 IS NULL) OR (col1 = @Param1)) AND
((@Param2 IS NULL) OR (col2 = @Param2)) AND
((@Param3 IS NULL) OR (col3 = @Param3))

So when I execute I can do something like this:

exec TestProc @Param1 = '111', @Param2 = '222' or
exec TestProc @Param1 = '111' or
exec TestProc @Param2 = '222'

and get different results.

Thanks again!

Ever heard of NULLIF or COALESCE? So, instead of:

(@Param1 IS NULL) OR (col1 = @Param1)

...you could have

col1 = coalesce(@Param1,col1)