I have an application that has a gridview. The population of this particular gridview is optional, so when the page is saved, if the gridview is not populated, it throws an error in the stored proc, stating too many parameters. So, the question is, how do I assign null values to the stored proc when the gridview is not populated? The user would check a box, then populate the gridview, otherwise, the checkbox is not checked and null values should be assigned to the gridview fields through the stored proc.
1
-
post your sproc param declaration SQL codeT McKeown– T McKeown2016-09-13 17:29:58 +00:00Commented Sep 13, 2016 at 17:29
Add a comment
|
2 Answers
SQL Server stored procedures (I assume SQL Server, you don't specify in your Q) can give default values to your parameters as so:
I am making up scenario because your question is lacking details
CREATE PROCEDURE dbo.ProcName(
@Name VARCHAR(50) = NULL //<- default to NULL
)
AS
BEGIN
IF @Name IS NULL BEGIN
SELECT TOP(1) * FROM dbo.Table
END
ELSE BEGIN
SELECT * FROM dbo.Table WHERE Name LIKE @Name + '%'
END
END
Default parameter values are ONLY used if the parameter is NOT PASSED, if you pass @Name then the procedure will use what you send.
Execution: In code you simply do not add parameters and let the stored procedure handle it.
3 Comments
T McKeown
ok, it obviously depends on the size of param list, but defaults are nice and my point about not sending the param is the only way to use a default parameter value
Andrew Morton
@TMcKeown That is an interesting idea. What is the effect on cached execution plans if you don't pass a parameter: does SQL Server make a new plan for a query where a parameter is not passed?
T McKeown
SQL server plans are based on what parameters are passed so it could affect it but without knowledge of your indices/schema it's hard for me to say.
Thanks to all..the @Name VARCHAR(50) = NULL is what I was looking for..