I don't want user dynamic sql

Dear all,
I have a stored procedure which might do INSERT, UPDATE over a specified
table.
Issue is that, at the outset, that table is totally unknown:
' into aux_PLAZO_PRIMER_IMPAGO ' +
' from ' + @.TABLA_COB
EXEC
..
' end DISC043 ' +
' into aux_PLAZO_PRIMER_IMPAGO ' +
' from ' + @.TABLA_COB
EXEC...
BLA,BLA,
I'm looking for a best version of that, dangerous dynamic sql is not well
welcomed here so that...
Declaring a variable as table also to solve the problem.
Thanks in advance for any input,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Enric
The best solution is know a table name that you operate on.
http://www.sommarskog.se/dynamic_sql.html
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:72F7C1B3-7C25-414A-8957-D9B0472476D6@.microsoft.com...
> Dear all,
> I have a stored procedure which might do INSERT, UPDATE over a specified
> table.
> Issue is that, at the outset, that table is totally unknown:
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC
> ..
> ' end DISC043 ' +
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC...
> BLA,BLA,
> I'm looking for a best version of that, dangerous dynamic sql is not well
> welcomed here so that...
> Declaring a variable as table also to solve the problem.
> Thanks in advance for any input,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||not sure if this is what you're looking for, but sp_executesql is the
first step to prevent SQL injection attacks. after that you're next
best step would be to validate the table against the sysobjects table
to ensure that it is a real table.|||I'm not sure I agree with what he says on that link. IMO he uses exec
way too much. His reasons for not using sp_executesql do not make
sense, and while he does suggest using quotename(), stored procedures
are a better way to ensure that your parameters are unable to be
anything other than the datatype suggested.|||Create as many INSERT and UPDATE procedures as there are tables, that's the
best advice any one can (and should) give you.
If you don't care about data, however, you could just as well store it all
in a single table. (NOT RECOMMENDED!)
I see no reason for dynamic SQL in such elementary processes as inserting,
updating or deleteing a row in a table - building the query string might eve
n
take longer than the execution (i.e. if you decide to do it right - clean up
the parameters to prevent SQL injection, check whether objects exist,
validating the parameters that contain actual values, etc.).
ML
http://milambda.blogspot.com/|||I don't know if this is the situation, but creating a generic insert /
update procedure for tables that store different types of objects is a bad
idea. Even if it's not the case now, as time goes on, there will be a need
to implment additional table specific parameters, data validation
programming, etc. and the procedure will become a big pile of unmanageable
spaghetti. Business rules for things like data validation and referential
integrity are best embedded at the table level in the form of constraints
and triggers.
Generic data access programming is best implmented on the application side
in the form of a data access class.
Designing Data Tier Components and Passing Data Through Tiers:
http://msdn.microsoft.com/library/d...Gag
.asp
On the other hand, if these tables store the same object (vertical
paritioning), then you can implement a partitioned view and perform inserts
/ updates into that:
Modifying Data in Partitioned Views:
http://msdn2.microsoft.com/en-us/library/ms187067.aspx
Strategies for Partitioning Relational Data Warehouses in Microsoft SQL
Server:
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
That said; if dynamic SQL can't be avoided, here my links on the topic of
SQL injection:
http://www.sqlservercentral.com/col...qlinjection.asp
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.nextgenss.com/papers/adv...l_injection.pdf
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:72F7C1B3-7C25-414A-8957-D9B0472476D6@.microsoft.com...
> Dear all,
> I have a stored procedure which might do INSERT, UPDATE over a specified
> table.
> Issue is that, at the outset, that table is totally unknown:
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC
> ..
> ' end DISC043 ' +
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC...
> BLA,BLA,
> I'm looking for a best version of that, dangerous dynamic sql is not well
> welcomed here so that...
> Declaring a variable as table also to solve the problem.
> Thanks in advance for any input,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||Will (william_pegg@.yahoo.co.uk) writes:
> I'm not sure I agree with what he says on that link. IMO he uses exec
> way too much. His reasons for not using sp_executesql do not make
> sense, and while he does suggest using quotename(), stored procedures
> are a better way to ensure that your parameters are unable to be
> anything other than the datatype suggested.
What I try to say about this particular case, is that that you should
not do this at all. That is, you should have one procedure per table.
I can't recall that I suggest that EXEC() should be used over sp_executesql,
but you are right that the text could be stronger on using sp_executesql,
and most of all using parameters. In fact, I'm already working with
reworking the article with a lot more emphasis on sp_executesql.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I do not want to see the results of an empty result set.

I use dynamic execution of SQL.
During the writing of this question I came up a solution, so thanks for your
attention, a solution is at the end of this message. Maybe there is a more
elegant solution.
declare @.work varchar(8000)
set @.work = 'select * from an_example_table where x = 1234'
exec @.work
And I only want to see result sets which contain rows, I do not want to see
the sets not containing rows.
(I do not want to see the hundreds of empty selections, offcourse the string
for @.work is generated).
Is there an elegant and simple solution to this problem ?
Ben Brugman.
Things I have tried
The string in work is build dynamically. I do this for hundreds of tables.
But most tables do not return a result, a count(*) would return a 0 (zero).
If I do an exec of the string and pick up the @.@.row_count there is allready
some output, which I do not want.
I tried to use a variable to count the number of rows, but I can not get
this variable outside the exec statement.
set @.work = 'select @.counter = count(*) from an_example_table where x = 1234'
the above does not work
set @.work = 'declare @.counter int select @.counter = count(*) from
an_example_table where x = 1234'
But now @.counter can not be used to test upon.
If I do not use @.counter, the information of 'empty' result set get
returned.
Is there an elegant simple solution to this problem.
SOLUTION
During the typing I came up with the following working not so elegant
solutions :
set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
an_example_table where x = 1234'
exec (@.work2)
set @.counter = @.@.rowcount
IF @.counter <> 0 DO SOMETHING
Thanks for your time and attention just formulating the question has brought
the anwser. You being there has been enough.Ben
declare @.x nvarchar(4000)
declare @.res int
set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
where customerid=''v''' +
')'
exec sp_executesql @.x, N'@.res int output', @.res = @.res output
if @.res >0
print 'yes'
"ben brugman" <ben@.niethier.nl> wrote in message
news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>I use dynamic execution of SQL.
> During the writing of this question I came up a solution, so thanks for
> your attention, a solution is at the end of this message. Maybe there is a
> more elegant solution.
> declare @.work varchar(8000)
> set @.work = 'select * from an_example_table where x = 1234'
> exec @.work
> And I only want to see result sets which contain rows, I do not want to
> see the sets not containing rows.
> (I do not want to see the hundreds of empty selections, offcourse the
> string for @.work is generated).
> Is there an elegant and simple solution to this problem ?
> Ben Brugman.
> Things I have tried
> The string in work is build dynamically. I do this for hundreds of tables.
> But most tables do not return a result, a count(*) would return a 0
> (zero).
> If I do an exec of the string and pick up the @.@.row_count there is
> allready some output, which I do not want.
> I tried to use a variable to count the number of rows, but I can not get
> this variable outside the exec statement.
> set @.work = 'select @.counter = count(*) from an_example_table where x => 1234'
> the above does not work
> set @.work = 'declare @.counter int select @.counter = count(*) from
> an_example_table where x = 1234'
> But now @.counter can not be used to test upon.
> If I do not use @.counter, the information of 'empty' result set get
> returned.
> Is there an elegant simple solution to this problem.
> SOLUTION
> During the typing I came up with the following working not so elegant
> solutions :
> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
> an_example_table where x = 1234'
> exec (@.work2)
> set @.counter = @.@.rowcount
> IF @.counter <> 0 DO SOMETHING
> Thanks for your time and attention just formulating the question has
> brought the anwser. You being there has been enough.
>|||Thanks I used your implementation, which has the advantage of delivering the
actual count.
Thanks,
Ben
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udEbrMXOHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Ben
> declare @.x nvarchar(4000)
> declare @.res int
> set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
> where customerid=''v''' +
> ')'
> exec sp_executesql @.x, N'@.res int output', @.res = @.res output
> if @.res >0
> print 'yes'
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>>I use dynamic execution of SQL.
>> During the writing of this question I came up a solution, so thanks for
>> your attention, a solution is at the end of this message. Maybe there is
>> a more elegant solution.
>> declare @.work varchar(8000)
>> set @.work = 'select * from an_example_table where x = 1234'
>> exec @.work
>> And I only want to see result sets which contain rows, I do not want to
>> see the sets not containing rows.
>> (I do not want to see the hundreds of empty selections, offcourse the
>> string for @.work is generated).
>> Is there an elegant and simple solution to this problem ?
>> Ben Brugman.
>> Things I have tried
>> The string in work is build dynamically. I do this for hundreds of
>> tables. But most tables do not return a result, a count(*) would return a
>> 0 (zero).
>> If I do an exec of the string and pick up the @.@.row_count there is
>> allready some output, which I do not want.
>> I tried to use a variable to count the number of rows, but I can not get
>> this variable outside the exec statement.
>> set @.work = 'select @.counter = count(*) from an_example_table where x =>> 1234'
>> the above does not work
>> set @.work = 'declare @.counter int select @.counter = count(*) from
>> an_example_table where x = 1234'
>> But now @.counter can not be used to test upon.
>> If I do not use @.counter, the information of 'empty' result set get
>> returned.
>> Is there an elegant simple solution to this problem.
>> SOLUTION
>> During the typing I came up with the following working not so elegant
>> solutions :
>> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
>> an_example_table where x = 1234'
>> exec (@.work2)
>> set @.counter = @.@.rowcount
>> IF @.counter <> 0 DO SOMETHING
>> Thanks for your time and attention just formulating the question has
>> brought the anwser. You being there has been enough.
>>
>

I do not want to see the results of an empty result set.

I use dynamic execution of SQL.
During the writing of this question I came up a solution, so thanks for your
attention, a solution is at the end of this message. Maybe there is a more
elegant solution.
declare @.work varchar(8000)
set @.work = 'select * from an_example_table where x = 1234'
exec @.work
And I only want to see result sets which contain rows, I do not want to see
the sets not containing rows.
(I do not want to see the hundreds of empty selections, offcourse the string
for @.work is generated).
Is there an elegant and simple solution to this problem ?
Ben Brugman.
Things I have tried
The string in work is build dynamically. I do this for hundreds of tables.
But most tables do not return a result, a count(*) would return a 0 (zero).
If I do an exec of the string and pick up the @.@.row_count there is allready
some output, which I do not want.
I tried to use a variable to count the number of rows, but I can not get
this variable outside the exec statement.
set @.work = 'select @.counter = count(*) from an_example_table where x =
1234'
the above does not work
set @.work = 'declare @.counter int select @.counter = count(*) from
an_example_table where x = 1234'
But now @.counter can not be used to test upon.
If I do not use @.counter, the information of 'empty' result set get
returned.
Is there an elegant simple solution to this problem.
SOLUTION
During the typing I came up with the following working not so elegant
solutions :
set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
an_example_table where x = 1234'
exec (@.work2)
set @.counter = @.@.rowcount
IF @.counter <> 0 DO SOMETHING
Thanks for your time and attention just formulating the question has brought
the anwser. You being there has been enough.Ben
declare @.x nvarchar(4000)
declare @.res int
set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
where customerid=''v''' +
')'
exec sp_executesql @.x, N'@.res int output', @.res = @.res output
if @.res >0
print 'yes'
"ben brugman" <ben@.niethier.nl> wrote in message
news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>I use dynamic execution of SQL.
> During the writing of this question I came up a solution, so thanks for
> your attention, a solution is at the end of this message. Maybe there is a
> more elegant solution.
> declare @.work varchar(8000)
> set @.work = 'select * from an_example_table where x = 1234'
> exec @.work
> And I only want to see result sets which contain rows, I do not want to
> see the sets not containing rows.
> (I do not want to see the hundreds of empty selections, offcourse the
> string for @.work is generated).
> Is there an elegant and simple solution to this problem ?
> Ben Brugman.
> Things I have tried
> The string in work is build dynamically. I do this for hundreds of tables.
> But most tables do not return a result, a count(*) would return a 0
> (zero).
> If I do an exec of the string and pick up the @.@.row_count there is
> allready some output, which I do not want.
> I tried to use a variable to count the number of rows, but I can not get
> this variable outside the exec statement.
> set @.work = 'select @.counter = count(*) from an_example_table where x =
> 1234'
> the above does not work
> set @.work = 'declare @.counter int select @.counter = count(*) from
> an_example_table where x = 1234'
> But now @.counter can not be used to test upon.
> If I do not use @.counter, the information of 'empty' result set get
> returned.
> Is there an elegant simple solution to this problem.
> SOLUTION
> During the typing I came up with the following working not so elegant
> solutions :
> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
> an_example_table where x = 1234'
> exec (@.work2)
> set @.counter = @.@.rowcount
> IF @.counter <> 0 DO SOMETHING
> Thanks for your time and attention just formulating the question has
> brought the anwser. You being there has been enough.
>|||Thanks I used your implementation, which has the advantage of delivering the
actual count.
Thanks,
Ben
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udEbrMXOHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Ben
> declare @.x nvarchar(4000)
> declare @.res int
> set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
> where customerid=''v''' +
> ')'
> exec sp_executesql @.x, N'@.res int output', @.res = @.res output
> if @.res >0
> print 'yes'
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>sql

I am Really confusing Please help me about a single query

Hi Guys,
This is my Problem.
A table contain following desing for handling different level of categories, bu it is dynamic

int_categoryid,
int_parent_categoryid,
int_categorylevel,
str_categoryname,
bit_active
thatall.
I want to list data from table as following order
categorry_parent11
category_child12
category_child13
category_child23
category_child22
categorry_parent21
..................
................................
.....................
like this..
ie we can insert parent category and sub category to n level dynamically without adding a new table
please mail me for another clarification...

please help me
regards
Abdul
For the example data you provided, can you tell us what data appears in the int_categorylevel column for each row?
categorry_parent11
category_child12
category_child13
category_child23
category_child22
categorry_parent21
|||

Thanks, tmorton
yes I am also interesting in int_categorylevel, and it gives the information about category level, 0,1,2 etc...
for Example Suppose we can take hotel menu categories...
Lunch Catgeroy level is 0
Veg Catgeroy level is 1
Average Catgeroy level is 2
Expensive Catgeroy level is 2
Non Veg Catgeroy level is 1
Chicken Catgeroy level is 2
Mutton Catgeroy level is 2
thanks ur response...