build sql script: dynamically

Showing posts with label dynamically. Show all posts

Tuesday, February 14, 2012

Building View Dynamically .. Need Help!

This is my resultset
Value Name
1 A
101 A
2 B
10 B
70 B
But I want it in this way from SQL Server
A B
1 2
101 10
70hangar18 wrote:
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
You should probably do this in your client application, but, run this script
in query analyzer:
set nocount on
select 1 Value,'A' [Name] into #temp
union all select
101 ,'A'
union all select
2 ,'B'
union all select
10 ,'B'
union all select
70 ,'B'
select * from #temp
Select
ta.A,
tb.B
From
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [A]
FROM #temp t1
WHERE t1.Name='A') ta
Full outer join
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [B]
FROM #temp t1
WHERE t1.Name='B') tb
ON ta.ID=tb.ID
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE TABLE #Test
(
col INT,
col1 CHAR(1)
)
INSERT INTO #Test VALUES (1,'A')
INSERT INTO #Test VALUES (20,'A')
INSERT INTO #Test VALUES (100,'A')
INSERT INTO #Test VALUES (10,'B')
INSERT INTO #Test VALUES (5,'B')
INSERT INTO #Test VALUES (3,'B')
SELECT
CASE WHEN col1 ='A' THEN col END AS 'A',
CASE WHEN col1 ='B' THEN col END AS 'B'
FROM #Test
"hangar18" <soni.somarajan@.wipro.com> wrote in message
news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
>|||That was my first thought as well, but it results in:
A B
1 [NULL]
101 [NULL]
[NULL] 2
[NULL] 10
[NULL] 70
Not quite what the OP wants.
Bob
Uri Dimant wrote:
> CREATE TABLE #Test
> (
> col INT,
> col1 CHAR(1)
> )
> INSERT INTO #Test VALUES (1,'A')
> INSERT INTO #Test VALUES (20,'A')
> INSERT INTO #Test VALUES (100,'A')
> INSERT INTO #Test VALUES (10,'B')
> INSERT INTO #Test VALUES (5,'B')
> INSERT INTO #Test VALUES (3,'B')
>
> SELECT
> CASE WHEN col1 ='A' THEN col END AS 'A',
> CASE WHEN col1 ='B' THEN col END AS 'B'
> FROM #Test
>
>
>
> "hangar18" <soni.somarajan@.wipro.com> wrote in message
> news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 1 Dec 2005 05:36:56 -0800, hangar18 wrote:

>This is my resultset
>Value Name
>1 A
>101 A
>2 B
>10 B
>70 B
>But I want it in this way from SQL Server
>A B
>1 2
>101 10
> 70
Hi hangar18,
Bob's post will work. But just for fun, here's another possibility:
SELECT MAX(A) AS A, MAX(B) AS B
FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
CASE WHEN a.col1 ='B' THEN a.col END AS B,
(SELECT COUNT(*)
FROM #Test AS b
WHERE b.col1 = a.col1
AND b.col <= a.col) AS Rank
FROM #Test AS a) AS d
GROUP BY Rank
ORDER BY Rank
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The basic principle of a tiered architecture is that display and
formatting is done in the front end and ** never** in the back end.
This a more basic programming principle than just SQL and RDBMS. This
should have been covered in the first year of your comp sci courses.|||When are you going to learn, perhaps you should actually do some
programming!
Formatting should be done where it is most efficient to do it, you can not
blankly state formatting be done in the front end and **never** the backend
its just not true - any programmer knows that.
Data manipulation is best done in the SQL Server and may well include
formatting, consider - paging, pivoting, security to name but a couple.
I think it is very irresponsible for you to keep taking this line when so
many times myself and other posters have given plenty of examples of when to
do formatting in the engine.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133490026.932802.161270@.o13g2000cwo.googlegroups.com...
> The basic principle of a tiered architecture is that display and
> formatting is done in the front end and ** never** in the back end.
> This a more basic programming principle than just SQL and RDBMS. This
> should have been covered in the first year of your comp sci courses.
>|||Hugo Kornelis wrote:
> SELECT MAX(A) AS A, MAX(B) AS B
> FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
> CASE WHEN a.col1 ='B' THEN a.col END AS B,
> (SELECT COUNT(*)
> FROM #Test AS b
> WHERE b.col1 = a.col1
> AND b.col <= a.col) AS Rank
> FROM #Test AS a) AS d
> GROUP BY Rank
> ORDER BY Rank
>
Clever!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Sunday, February 12, 2012

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
string,
> is
> > there still an advantage to using stored procedures (over just executing
> it
> > directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let the
SP
> > build the Sql string. Either way though, the Select statement will be a
> > variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of case?
> The
> > result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> >
> >
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
> > string, is there still an advantage to using stored procedures (over
> > just executing it directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let
> > the SP build the Sql string. Either way though, the Select statement
> > will be a variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of
> > case? The result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> John Smith wrote:
>> Hey folks,
>> I've got a select statement that gets built dynamically in code.
>> If what I pass to a stored procedure is nothing but a string that
>> is a select statement, and then have the stored procedure execute
>> that string, is there still an advantage to using stored procedures
>> (over just executing it directly from code).
>> The other possibility is to pass other parameters to the SP and let
>> the SP build the Sql string. Either way though, the Select
>> statement will be a variable string that gets executed.
>> Is there still a case for using stored procedures in this type of
>> case? The result set size will range from 1 record to 50,000
>> records.
>> Thanks!
>> Having the SP build the SQL is fine. Just use sp_executesql to
>> execute the call, and if the query has parameters, then use the
>> parameter feature of sp_executesql to define them and pass them to
>> the SQL statement. That way, if similar statements get executed, the
>> plan will already be in cache.
>> Performance will be fine... assuming your queries are tuned properly.
>>
>> --
>> David G.
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
--
David G.

Tuesday, February 14, 2012

Building View Dynamically .. Need Help!

Sunday, February 12, 2012

Building a Select Statement and SPs

Building a Select Statement and SPs

Building a Select Statement and SPs

build sql script

Blog Archive

About Me