Prev: EXECUTE permission denied on object 'sp_send_dbmail', database
Next: Sql Record number
From: Erland Sommarskog on 11 Oct 2009 10:31
Peter Hemmingsen (peter(a)hemmingsens.dk) writes:
> Why shouldn't I use dynamic SQL for this. The list of status values I use
> have a maximum of 5 numbers.

Because there is absolutely no reason. With dynamic SQL you lose a lot of
the benfits of stored procedures, for instance the possibility to revoke
users of direct permissions on the tables, and only give them access through
stored procedures.

There are certainly situations when dynamic SQL is worth all the hassle
it buys, but this is not one them.

And, yeah, there is another article on my web site about dynamic SQL...

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Peter Hemmingsen on 11 Oct 2009 10:38
Ok. I'll read both articles carefully first - thanks.

Peter

> Because there is absolutely no reason. With dynamic SQL you lose a lot of
> the benfits of stored procedures, for instance the possibility to revoke
> users of direct permissions on the tables, and only give them access
> through
> stored procedures.
>
> There are certainly situations when dynamic SQL is worth all the hassle
> it buys, but this is not one them.
>
> And, yeah, there is another article on my web site about dynamic SQL...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Peter Hemmingsen on 12 Oct 2009 04:24
All

Thanks a lot for all your comments and suggestions it has been very helpful.
As Erland correctly pointed out the routine is for users supplying a number
of search criteria.
I understand that there may bee a better way to design this and I'll look
into this later. At this stage it works very well with Erlands routine.


Again - thanks for your help and comments.

Peter


From: Peter Hemmingsen on 12 Oct 2009 04:40
Hi Erland

Our declaration of function iter$simple_intlist_to_tbl must be slightly
different.

I copied the declaration (and implementation) from your article:

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS

and because of the NOT NULL it fails. I've changed it to:

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int) AS

and surely enough it now works with NULL.

Thanks for all your help and good articles!!

Peter


From: Erland Sommarskog on 12 Oct 2009 17:50
Peter Hemmingsen (phe(a)softco.dk) writes:
> Our declaration of function iter$simple_intlist_to_tbl must be slightly
> different.
>
> I copied the declaration (and implementation) from your article:
>
> CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
> RETURNS @tbl TABLE (number int NOT NULL) AS
>
> and because of the NOT NULL it fails. I've changed it to:
>
> CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
> RETURNS @tbl TABLE (number int) AS
>
> and surely enough it now works with NULL.

Indeed, I just tried in one of my test databases for arrays and lists,
but I see that in the tests I use a space-separated list, and this function
can handle NULL input. I should fix the one that appear in the article as
well, I think!

Thanks for pointing this out!


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: EXECUTE permission denied on object 'sp_send_dbmail', database
Next: Sql Record number