|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
HowTo:Dynamically Inject a Select Query Into UDFBelow is a UDF the intent of which i think is pretty clear. Trouble is it doesn't work because of the attempt to dynamically inject/build select query for cursor. Any help much appreciated. Ian CREATE FUNCTION dbo.ACEor(@ACEId int, @Authority nvarchar(100)) RETURNS int AS BEGIN DECLARE @SQL varchar(300) DECLARE @iAccum INT , @iWork INT SET @iAccum = 0 /* Fail Start : This is the bit that fails but its also precisely what i need to do */ SET @SQL = 'SELECT AuthLevel FROM tblSecurityPermission WHERE ACEId = @ACEId AND RoleId IN (' + @Authority + ') ' DECLARE dataBitsCursor CURSOR FOR EXEC(@SQL) /* Fail End */ /* The rest works fine */ OPEN dataBitsCursor FETCH dataBitsCursor INTO @iWork WHILE 0 = @@fetch_status BEGIN SET @iAccum = @iAccum | @iWork FETCH dataBitsCursor INTO @iWork END CLOSE dataBitsCursor DEALLOCATE dataBitsCursor RETURN @iAccum END Check this article:
http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings Read the whole thing, but the function at the bookmark is basically what you want to use to resolve your in criteria, then join the function to your table. This is usually an acceptable solution. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Ian Evitable" <wh***@hog.com> wrote in message news:uPpr5mctFHA.2792@tk2msftngp13.phx.gbl... > Hello, > > Below is a UDF the intent of which i think is pretty clear. Trouble is it > doesn't work because of the attempt to dynamically inject/build select > query > for cursor. Any help much appreciated. > > Ian > > > CREATE FUNCTION dbo.ACEor(@ACEId int, @Authority nvarchar(100)) RETURNS > int > AS BEGIN > > DECLARE @SQL varchar(300) > DECLARE @iAccum INT , @iWork INT > > SET @iAccum = 0 > > /* Fail Start : This is the bit that fails but its also precisely what i > need to do */ > SET @SQL = 'SELECT AuthLevel FROM tblSecurityPermission WHERE ACEId = > @ACEId > AND RoleId IN (' + @Authority + ') ' > DECLARE dataBitsCursor CURSOR FOR EXEC(@SQL) > /* Fail End */ > > /* The rest works fine */ > OPEN dataBitsCursor > FETCH dataBitsCursor INTO @iWork > > WHILE 0 = @@fetch_status > BEGIN > SET @iAccum = @iAccum | @iWork > FETCH dataBitsCursor INTO @iWork > END > > CLOSE dataBitsCursor > DEALLOCATE dataBitsCursor > RETURN @iAccum END > > Chekc this KB article for the explanation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329329 -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Ian Evitable" <wh***@hog.com> wrote in message news:uPpr5mctFHA.2792@tk2msftngp13.phx.gbl... > Hello, > > Below is a UDF the intent of which i think is pretty clear. Trouble is it > doesn't work because of the attempt to dynamically inject/build select > query > for cursor. Any help much appreciated. > > Ian > > > CREATE FUNCTION dbo.ACEor(@ACEId int, @Authority nvarchar(100)) RETURNS > int > AS BEGIN > > DECLARE @SQL varchar(300) > DECLARE @iAccum INT , @iWork INT > > SET @iAccum = 0 > > /* Fail Start : This is the bit that fails but its also precisely what i > need to do */ > SET @SQL = 'SELECT AuthLevel FROM tblSecurityPermission WHERE ACEId = > @ACEId > AND RoleId IN (' + @Authority + ') ' > DECLARE dataBitsCursor CURSOR FOR EXEC(@SQL) > /* Fail End */ > > /* The rest works fine */ > OPEN dataBitsCursor > FETCH dataBitsCursor INTO @iWork > > WHILE 0 = @@fetch_status > BEGIN > SET @iAccum = @iAccum | @iWork > FETCH dataBitsCursor INTO @iWork > END > > CLOSE dataBitsCursor > DEALLOCATE dataBitsCursor > RETURN @iAccum END > > oops, wrong group/message :)
-- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message news:uNa3FCdtFHA.3596@TK2MSFTNGP15.phx.gbl... > Chekc this KB article for the explanation: > > http://support.microsoft.com/default.aspx?scid=kb;en-us;329329 > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > "Arguments are to be avoided: they are always vulgar and often > convincing." (Oscar Wilde) > > "Ian Evitable" <wh***@hog.com> wrote in message > news:uPpr5mctFHA.2792@tk2msftngp13.phx.gbl... >> Hello, >> >> Below is a UDF the intent of which i think is pretty clear. Trouble is it >> doesn't work because of the attempt to dynamically inject/build select >> query >> for cursor. Any help much appreciated. >> >> Ian >> >> >> CREATE FUNCTION dbo.ACEor(@ACEId int, @Authority nvarchar(100)) RETURNS >> int >> AS BEGIN >> >> DECLARE @SQL varchar(300) >> DECLARE @iAccum INT , @iWork INT >> >> SET @iAccum = 0 >> >> /* Fail Start : This is the bit that fails but its also precisely what i >> need to do */ >> SET @SQL = 'SELECT AuthLevel FROM tblSecurityPermission WHERE ACEId = >> @ACEId >> AND RoleId IN (' + @Authority + ') ' >> DECLARE dataBitsCursor CURSOR FOR EXEC(@SQL) >> /* Fail End */ >> >> /* The rest works fine */ >> OPEN dataBitsCursor >> FETCH dataBitsCursor INTO @iWork >> >> WHILE 0 = @@fetch_status >> BEGIN >> SET @iAccum = @iAccum | @iWork >> FETCH dataBitsCursor INTO @iWork >> END >> >> CLOSE dataBitsCursor >> DEALLOCATE dataBitsCursor >> RETURN @iAccum END >> >> > > >> This is the bit that fails but its also precisely what i need to do << No, you are showing us what you are doing; you have not told us whatyou want to happen. A spec tells us what and not how. You have dynamic SQL, cursors, string lists, bit manipulations and improper names on the data elements. All of these things are really bad SQL programming practices. You can have up to 1024 parameters, so you you do not have to disassemble a string to fake a long parameter list. Your routine probably ought to look more like this: CREATE FUNCTION ACEor (@my_ace_id INTEGER, @a1 INTEGER, @a2 INTEGER, @a3 INTEGER, .. @an INTEGER); RETURNS INTEGER SELECT auth_level FROM SecurityPermissions WHERE ace_id = @my_ace_id AND role_code IN (@a1, @a2, @a3, .. @an); SQL is compiled, not interpreted like BASIC. SQL is not assembly language, so you never do bit level stuff. SQL is declarative so we do not use loops. Your code looks like you are trying desparately to write some 3GL or OO language in SQL and not use SQL at all. "--CELKO--" <jcelko***@earthlink.net> wrote in message My intention was not to write specification but rather to communicate mynews:1126465902.563109.125950@g43g2000cwa.googlegroups.com... > >> This is the bit that fails but its also precisely what i need to do << > > No, you are showing us what you are doing; you have not told us what > you want to happen. A spec tells us what and not how. > problem in such a way that someone would be able to understand what i was trying to accomplish and offer me a method for doing so. It was successful so what your point? > You have dynamic SQL, cursors, string lists, bit manipulations and I disagree. I see no problem with breaking the rules so long as you know> improper names on the data elements. All of these things are really > bad SQL programming practices. > your breaking them and what the likely consequences of doing so might be. The dynamic Sql is not input by the user but rather the system. I dont forsee any way it can jump up and bite me. And the remainder of your concerns dont seem to apply. Show quote > You can have up to 1024 parameters, so you you do not have to Your ideas would have me constantly repeat the same jumbled mess throughout> disassemble a string to fake a long parameter list. Your routine > probably ought to look more like this: > > CREATE FUNCTION ACEor > (@my_ace_id INTEGER, @a1 INTEGER, @a2 INTEGER, @a3 INTEGER, .. @an > INTEGER); > RETURNS INTEGER > SELECT auth_level > FROM SecurityPermissions > WHERE ace_id = @my_ace_id > AND role_code > IN (@a1, @a2, @a3, .. @an); > a number of stored procs in my application. My way allows for encapsulation and code reuse reducing application development and maintenance time. Your suggestion is messy and bloated.Whats more it requires that i dynamically assign parameters in my dal with cmdSelect .Parameters.Ad***@A1...etc which is a god awful approach. > SQL is compiled, not interpreted like BASIC. SQL is not assembly If we should never do bitwise operations then why has the functionality been> language, so you never do bit level stuff. made available? Most likely because there is a demand for it. Therefore you seem out of touch with what people actually do. Additonally the bitwise operations im doing with Sql allow me to only return those rows that a given user is authorised to access. Your suggestions would have me do the bitwising outside the Db. My application doesn't require OTT security but i still see no reason why i should bitwise in the middle tier when i can do so in the dbms. My way seems more secure and performant than yours for the purpose at hand. > SQL is declarative so we do Dont "we"? Then that cursor/loop im using must be a mirage. I gotta say it> not use loops. works pretty well though considering "we" dont use them. >Your code looks like you are trying desparately to Oh well. Dont look now but Microsoft have just embedded the entire CLR into> write some 3GL or OO language in SQL and not use SQL at all. > Sql Server 2005. Your ideas about how Sql is used, when and where seem outdated. I'd use Art Garfunkles monster mash language if it helped me store, maintain and return data from a database in a secure, performant and cost effective way. I couldn't actually care less about Sql per se. It just so happens that its the beast we all use and love. Im more interested in delivering solutions to my customers problems at a reasonable price point and in a reasonable period of time. You've pointed out the world according to you but you haven't actually communicated any "real" as opposed to "theoretical" benefit in doing it your way. When you can do that your replies may seem a little more constructive. Thanks Ian Hi Ian,
I know it hurts when someone points that you are wrong , but look it from the point of construction and gaining knowledge . You have dynamic SQL, cursors, string lists, bit manipulations and improper names on the data elements. All of these things are really bad SQL programming practices Even Erland the authority on Dynamic SQL will agree that it is not a good programming practice. There is no need for dynamic sql here You may try to do this to solve the problem. Create Table Params ( vRole int -- I am taking only one just to prove the point. ) insert into Params values(2) insert into Params values(5) insert into Params values(7) insert into Params values(8) insert into Params values(9) ...... Even though I would suggest not to use cursors (They are only useful when you are interested in some Administration stuff),but if your process depends upon it you may use . Declare x cursor as Select * from yourtablename where roleid in (select vRole from Params) ......Processing .....Drop all the rows from Params Table I hope this help With warm regards Jatinder Singh Ian Evitable wrote: Show quote > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1126465902.563109.125950@g43g2000cwa.googlegroups.com... > > >> This is the bit that fails but its also precisely what i need to do << > > > > No, you are showing us what you are doing; you have not told us what > > you want to happen. A spec tells us what and not how. > > > > My intention was not to write specification but rather to communicate my > problem in such a way that someone would be able to understand what i was > trying to accomplish and offer me a method for doing so. It was successful > so what your point? > > > You have dynamic SQL, cursors, string lists, bit manipulations and > > improper names on the data elements. All of these things are really > > bad SQL programming practices. > > > > I disagree. I see no problem with breaking the rules so long as you know > your breaking them and what the likely consequences of doing so might be. > The dynamic Sql is not input by the user but rather the system. I dont > forsee any way it can jump up and bite me. And the remainder of your > concerns dont seem to apply. > > > You can have up to 1024 parameters, so you you do not have to > > disassemble a string to fake a long parameter list. Your routine > > probably ought to look more like this: > > > > CREATE FUNCTION ACEor > > (@my_ace_id INTEGER, @a1 INTEGER, @a2 INTEGER, @a3 INTEGER, .. @an > > INTEGER); > > RETURNS INTEGER > > SELECT auth_level > > FROM SecurityPermissions > > WHERE ace_id = @my_ace_id > > AND role_code > > IN (@a1, @a2, @a3, .. @an); > > > > Your ideas would have me constantly repeat the same jumbled mess throughout > a number of stored procs in my application. My way allows for encapsulation > and code reuse reducing application development and maintenance time. Your > suggestion is messy and bloated.Whats more it requires that i dynamically > assign parameters in my dal > > with cmdSelect > .Parameters.Ad***@A1...etc > > which is a god awful approach. > > > SQL is compiled, not interpreted like BASIC. SQL is not assembly > > language, so you never do bit level stuff. > > If we should never do bitwise operations then why has the functionality been > made available? Most likely because there is a demand for it. Therefore you > seem out of touch with what people actually do. > > Additonally the bitwise operations im doing with Sql allow me to only return > those rows that a given user is authorised to access. Your suggestions would > have me do the bitwising outside the Db. My application doesn't require OTT > security but i still see no reason why i should bitwise in the middle tier > when i can do so in the dbms. My way seems more secure and performant than > yours for the purpose at hand. > > > SQL is declarative so we do > > not use loops. > > Dont "we"? Then that cursor/loop im using must be a mirage. I gotta say it > works pretty well though considering "we" dont use them. > > >Your code looks like you are trying desparately to > > write some 3GL or OO language in SQL and not use SQL at all. > > > > Oh well. Dont look now but Microsoft have just embedded the entire CLR into > Sql Server 2005. Your ideas about how Sql is used, when and where seem > outdated. I'd use Art Garfunkles monster mash language if it helped me > store, maintain and return data from a database in a secure, performant and > cost effective way. I couldn't actually care less about Sql per se. It just > so happens that its the beast we all use and love. > > Im more interested in delivering solutions to my customers problems at a > reasonable price point and in a reasonable period of time. You've pointed > out the world according to you but you haven't actually communicated any > "real" as opposed to "theoretical" benefit in doing it your way. When you > can do that your replies may seem a little more constructive. > > Thanks > > Ian "jsfromynr" <jatinder.si***@clovertechnologies.com> wrote in message The point is he didn't actually convey any knowledge.news:1126503349.506259.166950@g47g2000cwa.googlegroups.com... > Hi Ian, > I know it hurts when someone points that you are wrong , but look > it from the point of construction and gaining knowledge . > Show quote > There is no need for dynamic sql here Unfortunately no. Your example makes no sense to me. Youve constructed a> You may try to do this to solve the problem. > Create Table Params > ( > vRole int -- I am taking only one just to prove the point. > ) > insert into Params values(2) > insert into Params values(5) > insert into Params values(7) > insert into Params values(8) > insert into Params values(9) > ..... > > Even though I would suggest not to use cursors (They are only useful > when you are interested in some Administration stuff),but if your > process depends upon it you may use . > > > Declare x cursor as Select * from yourtablename where roleid in (select > vRole from Params) > > .....Processing > > ....Drop all the rows from Params Table > > I hope this help > table using hardcoded values. Where do these values come from? What do they mean? Is this some form of auxillary table? And you've then used a cursor after telling me i dont need to use a cursor? It doesn't hurt to be told im wrong at all but i would expect that you explain why and provide an attractive/more beneficial alternative. How does your example provide any kind of accessLevel? Ive already acknowledeged that it's best practise... but the kind of alternatives CELKO suggested are in my opinion messy and bloated. For future reference the function i posted was plucked from the following Url: http://www.windowsitpro.com/SQLServer/Articles/ArticleID/21079/pg/2/2.html Ian Ian Evitable wrote: Show quote > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > > news:1126465902.563109.125950@g43g2000cwa.googlegroups.com... > > > >> This is the bit that fails but its also precisely what i need to do << > > > > > > No, you are showing us what you are doing; you have not told us what > > > you want to happen. A spec tells us what and not how. > > > > > > > My intention was not to write specification but rather to communicate my > > problem in such a way that someone would be able to understand what i was > > trying to accomplish and offer me a method for doing so. It was successful > > so what your point? > > > > > You have dynamic SQL, cursors, string lists, bit manipulations and > > > improper names on the data elements. All of these things are really > > > bad SQL programming practices. > > > > > > > I disagree. I see no problem with breaking the rules so long as you know > > your breaking them and what the likely consequences of doing so might be. > > The dynamic Sql is not input by the user but rather the system. I dont > > forsee any way it can jump up and bite me. And the remainder of your > > concerns dont seem to apply. > > > > > You can have up to 1024 parameters, so you you do not have to > > > disassemble a string to fake a long parameter list. Your routine > > > probably ought to look more like this: > > > > > > CREATE FUNCTION ACEor > > > (@my_ace_id INTEGER, @a1 INTEGER, @a2 INTEGER, @a3 INTEGER, .. @an > > > INTEGER); > > > RETURNS INTEGER > > > SELECT auth_level > > > FROM SecurityPermissions > > > WHERE ace_id = @my_ace_id > > > AND role_code > > > IN (@a1, @a2, @a3, .. @an); > > > > > > > Your ideas would have me constantly repeat the same jumbled mess throughout > > a number of stored procs in my application. My way allows for encapsulation > > and code reuse reducing application development and maintenance time. Your > > suggestion is messy and bloated.Whats more it requires that i dynamically > > assign parameters in my dal > > > > with cmdSelect > > .Parameters.Ad***@A1...etc > > > > which is a god awful approach. > > > > > SQL is compiled, not interpreted like BASIC. SQL is not assembly > > > language, so you never do bit level stuff. > > > > If we should never do bitwise operations then why has the functionality been > > made available? Most likely because there is a demand for it. Therefore you > > seem out of touch with what people actually do. > > > > Additonally the bitwise operations im doing with Sql allow me to only return > > those rows that a given user is authorised to access. Your suggestions would > > have me do the bitwising outside the Db. My application doesn't require OTT > > security but i still see no reason why i should bitwise in the middle tier > > when i can do so in the dbms. My way seems more secure and performant than > > yours for the purpose at hand. > > > > > SQL is declarative so we do > > > not use loops. > > > > Dont "we"? Then that cursor/loop im using must be a mirage. I gotta say it > > works pretty well though considering "we" dont use them. > > > > >Your code looks like you are trying desparately to > > > write some 3GL or OO language in SQL and not use SQL at all. > > > > > > > Oh well. Dont look now but Microsoft have just embedded the entire CLR into > > Sql Server 2005. Your ideas about how Sql is used, when and where seem > > outdated. I'd use Art Garfunkles monster mash language if it helped me > > store, maintain and return data from a database in a secure, performant and > > cost effective way. I couldn't actually care less about Sql per se. It just > > so happens that its the beast we all use and love. > > > > Im more interested in delivering solutions to my customers problems at a > > reasonable price point and in a reasonable period of time. You've pointed > > out the world according to you but you haven't actually communicated any > > "real" as opposed to "theoretical" benefit in doing it your way. When you > > can do that your replies may seem a little more constructive. > > > > Thanks > > > > Ian > >> I disagree. I see no problem with breaking the rules so long as you know your breaking them and what the likely consequences of doing so might be. << Cowboy coder syndrome! In my day we used to say that we could writeFORTRAN in any language. Remember that I make a good part of my living cleaning up code like this. >> Additonally the bitwise operations im doing with SQLallow me to only return those rows that a given user is authorised to access. Your suggestions would have me do the bitwising outside the DB. << No, I would prefer to use the DCL and VIEWs for access control. Thatis what they were meant to do. But that requires learning the DCL sub-language instead of mimicking low-level code from your first programming languages. >> If we should never do bitwise operations then why has the functionality been made available? Most likely because there is a demand for it. Therefore you seem out of touch with what people actually do. << Why do you think that because MS exposed low-level code, it is a goodidea? It is for MS because you will never port that code, but it really hurts your customers. As a friend mine put it so concisely, "Just because you can, it does not mean your should. They make size 18 string bikinis, too." >> I'm more interested in delivering solutions to my customers problems at a reasonable price point and in a reasonable period of time.<< That is another "cowboy coder" phrase! Why didn't quality and andmaintaining the code (where the real cost of a system is found) come into consideration. If you know what you are doing, it is easier to write good code; about 80% of a programmer's time is spent de-bugging his own errors or poor design. >> Microsoft have just embedded the entire CLR into SQL Server 2005.<< And predict that this will be a mess. The DBAs will have to maintain adozen various languages with slightly different rules for bit flags, math function, string functions, etc. After about 20 years with SQL and a writing a few books on the language, I have written five cursors and I know that I could have avoided three of them if we had CASE expressions. This is why I doubt that your approach is a good one. "--CELKO--" <jcelko***@earthlink.net> wrote in message Remember that I make a good part of my livingnews:1126566268.239246.228520@f14g2000cwb.googlegroups.com... > cleaning up code like this. I wouldn;t worry. Theres no way my customers could afford you.;) Ian Evitable (wh***@hog.com) writes:
> Below is a UDF the intent of which i think is pretty clear. Trouble is And there is no need for this.> it doesn't work because of the attempt to dynamically inject/build > select query for cursor. Any help much appreciated. > /* Fail Start : This is the bit that fails but its also precisely what i Replace with> need to do */ > SET @SQL = 'SELECT AuthLevel FROM tblSecurityPermission WHERE ACEId = > @ACEId > AND RoleId IN (' + @Authority + ') ' > DECLARE dataBitsCursor CURSOR FOR EXEC(@SQL) > /* Fail End */ SELECT t.AuthLevel FROM tblSecurityPermission SP JOIN iter_intlist_to_table(@Authority) n ON SP.RoleID = n.Number WHERE SP.ACEID = @ACEid Where you find the table function on http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers. Note that this takes a space-separated list of numbers, but that easy to tweak. By the way, the way to use dynamic SQL for a cursor is: EXEC('DECLARE dataBitsCursor INSENSITIVE CURSOR FOR ' + @sql) (And always declare your cursors as INSENSITIVE to avoid nasty surprises.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp  |
|||||||||||||||||||||||
Other interesting topics