January 8, 2013 at 3:25 pm
Is this the standard way to deal with optional parameters in SP?
CREATE Procedure AddMonth
(
@EnterMonth as int,
@product int = null
)
AS
INSERT INTO X
SELECT FROM Y
WHERE Y.Month = @Month
and (@product is null or product = @product)
It just seems counter intuitive to say @product is null but I seems logical although cheap to me. I feel like I'll be rigiding something or using a very primitive and novice technique.
My other question is whether or not i can make a Table Value Parameter optional?
Thanks!
January 8, 2013 at 3:34 pm
Hi, a general rule for saying if a parameter i optoionl or not is the business that the stored procedure must do. For example if you need to fill a ProductID column in a table and this column is not nullable then you can make the parameter as NULL but the you have to find a value to enter to a table. This could be a default value for that column
The second question - TVP parameter can be null
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
January 8, 2013 at 3:38 pm
Jacob,
What you're doing is basically preparing the beginning of a catch-all query, of which there are two approaches. There's the easy to maintain version, which is what you're doing. The other is (once you have enough pieces in the where clause) to use a dynamic sql method. The discussion is best described in one of Gail Shaw's blog pieces, found here:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 8, 2013 at 3:44 pm
I can't speak to TVPs but I use this technique to create a default value in stored procedures and functions have seen it used quite often.
I have always been curious about if doing this increases, decreases or has no impact on the likeliness of parameter sniffing but have never tested it.
-- Itzik Ben-Gan 2001
January 8, 2013 at 3:55 pm
Thanks guys for the replies.
I'm going to check out the link Craig! Thx
Table Value Parameters seem like a lot of work. I don't fully understand how a user is supposed to know the number of columns are required to pass to the SP.
So What I want to do currently is pass a lot of ids to the SP or send one value via a optional parameter.
So the user would be able to use either the optional parameter if he only want to provide one value or he can provide a table of values if he wants to go through the trouble of creating a table variable and using inserts to insert his ids.
TVP just seem like a lot of upfront work on the user. (When i use a stored proc or funct I don't usually think i will need to write out a bunch of code just to pass it a value.)
Either way, I need to do it. I'm just tyring to figure out the best way. Thanks!
January 8, 2013 at 4:00 pm
Jacob Pressures (1/8/2013)
I'm going to check out the link Craig! Thx
No problem, my pleasure.
Table Value Parameters seem like a lot of work. I don't fully understand how a user is supposed to know the number of columns are required to pass to the SP.
They are, but, um... err... why are your USERS involved in your stored procedures? They should have a developed front end to work with. The developers shouldn't have as much issue with them. TVPs have a ton of their own issues, but that's a different headache of mine.
So What I want to do currently is pass a lot of ids to the SP or send one value via a optional parameter.
Ah, that's a comma delimited list. Usually best dealt with via a splitter. Check this article by Jeff Moden out:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
So the user would be able to use either the optional parameter if he only want to provide one value or he can provide a table of values if he wants to go through the trouble of creating a table variable and using inserts to insert his ids.
Again, users... in T-SQL. This is the beginning of a recurring nightmare I have. Don't do this. They need an interface, or you need larger bottles of Tequila. There's really not a lot of middle ground there.
What, exactly, are you trying to do? Not codewise, but as a business usage objective? What do your users NEED?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 8, 2013 at 4:15 pm
Sorry I don't mean end user. To me a user is who is going to be using my stored procedure. Perhaps i should say developers but that too is ambiguous to me and not clear. Not all developers have access to the interworking of your SP.
So these will be programmers who use my SP not businessmen who will use an application.
January 8, 2013 at 4:23 pm
Jacob Pressures (1/8/2013)
Sorry I don't mean end user. To me a user is who is going to be using my stored procedure. Perhaps i should say developers but that too is ambiguous to me and not clear. Not all developers have access to the interworking of your SP.So these will be programmers who use my SP not businessmen who will use an application.
Heh, and to most of us developer is much less ambiguous, but that's because when we speak of users we mean end users. No worries though now that we're on the same page.
Developers shouldn't care about your SP other than the parameters you expose to them, that's true. However, there's a few walkthroughs on MSDN that will walk a .NET developer through very easily working with your TVPs. I wouldn't worry so much about them being able to use them via a connection.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 9, 2013 at 8:23 am
Thanks very much for the information. I'm currently an intern. My supervisor is giving me the opportunity to weigh out different approaches.
He suggested that i could perhaps pass the SP or UDF a table name. Is the only way that can be done is through dynamic SQL?
I think I could use TVP for that but it would require that I pull in a bunch of data before passing it to the UDF or SP. I know I can't update the TVP variable, but I assume perhaps I can return a resultset with different values.
So the only options I can think of are Dynamic SQL and using a TVP. Are there any other options? I have a reference I need to read about the downside of D-SQL but I've not read it. And you stated some reasons you don't like TVP, do you have a link maybe. I'm sure i can google it otherwise.
Thanks for helping me map out my options.
Also thanks for the article at http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/. I read it yesterday and I'm going to re-read it today. it had some very valuable info and have given me some insight into how to evaluate the efficiency of a query.
Thanks!
January 9, 2013 at 8:35 am
Jacob Pressures (1/9/2013)
He suggested that i could perhaps pass the SP or UDF a table name. Is the only way that can be done is through dynamic SQL?
Yes, and passing procs the names of tables to query is a Really Bad Idea.
Procedures should be considered the same way that a front end method is. You wouldn't write a generic method that could operate on Customers, Employees or Products, depending on the class name passed to the method (or at least I hope you wouldn't), so don't do it in T-SQL procedures either
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2013 at 9:49 am
Ok thanks for the info. Right now I'm trying to avoid D-SQL until i learn more about it.
The TVP seems, at least in my head, like an alternative method although It would probably still not allow for query optimization and would put a lot of data in memory.
It appears to me that they have several different databases with the same column name or perhaps they are similar column names with similar issues. It would probably be better to have a SP custome made for each occurence but I'm just doing this based on his requests.
I'm open to any feedback. thanks!
January 9, 2013 at 9:55 am
What Gail said.
Stored procs are like an API to the database.
You have to identify the use cases for the database interactions and then define the stored proc interfaces (input/output params and result set(s)).
The developers then code against the interfaces you design and document.
January 9, 2013 at 9:59 am
Jacob Pressures (1/9/2013)
Ok thanks for the info. Right now I'm trying to avoid D-SQL until i learn more about it.The TVP seems, at least in my head, like an alternative method although It would probably still not allow for query optimization and would put a lot of data in memory.
It appears to me that they have several different databases with the same column name or perhaps they are similar column names with similar issues. It would probably be better to have a SP custome made for each occurence but I'm just doing this based on his requests.
I'm open to any feedback. thanks!
D-SQL has it's pro's and con's. Here's what I know about D-SQL for sure: the more you know about DML, DDL and set-based SQL development, the less you will need D-SQL. That's been my experience anyhow.
-- Itzik Ben-Gan 2001
January 13, 2013 at 9:46 am
Jacob Pressures (1/9/2013)
The TVP seems, at least in my head, like an alternative method although It would probably still not allow for query optimization and would put a lot of data in memory.
There are always tradeoffs. TVPs function as read-only table variables once inside the stored procedure so query optimization could be an issue if you are planning to pass thousands of rows in and then join to that table many times. In that case I would still recommend a TVP but would say to push the data from it into a #TempTable built to best support the queries in your proc, then run your proc code against the #TempTable.
Here is how the table type you are describing needing might be defined:
USE SampleDB;
GO
CREATE TYPE dbo.SampleTableType AS TABLE(
SampleID INT NOT NULL PRIMARY KEY
);
And here is how one might make a straight T-SQL call to that proc:
USE SampleDB;
DECLARE @tvp dbo.SampleTableType;
INSERT INTO @tvp
VALUES (99),
(101),
(377);
EXEC dbo.SampleProcedure
@tvp_parameter_name = @tvp;
Not onerous at all in my opinion. Once inside the proc you will already have the data in a table accessible like any other table variable. TVPs are quite efficient when compared with alternative methods of passing sets to a proc including passing XML (because you have to shred the XML in the proc to get at the data) and delimited lists (because you have to split the list in the proc to get at the data) and TVPs are much more concise in my opinion.
If you support .NET developers they will love you for implementing TVPs because they have a natural alignment with the SqlClient. Essentially a .NET developer can pass an ADO.NET DataTable with the same definition as the TVP directly into a stored procedure via the SqlCommand.Parameters collection. It is a very slick technique that will save your developers a lot of time and it performs very well. One recommendation if you go with TVPs, make be sure they all have a primary key on them.
TVPs do have some limitations but they are worth investigating as the potential first option ahead of passing a string or xml-based list.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 9:33 pm
GilaMonster (1/9/2013)
Jacob Pressures (1/9/2013)
He suggested that i could perhaps pass the SP or UDF a table name. Is the only way that can be done is through dynamic SQL?Yes, and passing procs the names of tables to query is a Really Bad Idea.
Procedures should be considered the same way that a front end method is. You wouldn't write a generic method that could operate on Customers, Employees or Products, depending on the class name passed to the method (or at least I hope you wouldn't), so don't do it in T-SQL procedures either
For the reasons you mention, I agree. I might be taking what you said incorrectly but, I'd rather say "It Depends". Although I certainly wouldn't try to write a generic proc to handle the different types of entities you cited, if you have many entities with identical structures, I've written code to pass table names to handle them in a generic fashion. It can even have some serious advantages over the likes of partitioned tables or partitioned views.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply