Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts

Wednesday, March 21, 2012

Dyncamic SQL

Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)

Wednesday, March 7, 2012

Dynamical XML as datasource.

Hi!

I'd like to know if I can pass a datasource as a parameter or better, if a datasource can be set for a report dinamically. The reason is that we're generating an xml dinamically and we would like to use this xml as our datasource. Is this possible?

Thanks in advance!!! :)

Are you looking for expression based connction strings, to have the connection string info for a datasource to be dynamic:

http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx

dynamic where clause

Hi

I need some advice on which direction to take!

Consider this statement:

SELECT business_name FROM myTable WHERE town = @.town AND county = @.county

My problem is that i will not always have the @.county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @.county variable is not available, it has a value of 0.

thanks again

Ps, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.SELECT business_name
FROM myTable
WHERE (town = @.town OR @.town IS NULL)
AND (county = @.county OR @.county = 0)
?|||Hi pootle

If the @.county has a value of 0 then i don't want to make it part of the WHERE clause, ie i dont want it to search the county column - in your example it would search all counties which are equal to 0?

This is what i'm trying to acheive, but only use one SELECT statment:

IF @.county <> '0'
BEGIN
SELECT business_name FROM myTable WHERE town = @.town AND county = @.county
END
ELSE
BEGIN
SELECT business_name FROM myTable WHERE town = @.town
END|||Hi Mattock

Did you try the code? I know the answer is no :) It does just what you ask for.|||lol, i'm real sorry, got the monday morning blues! Thick mode was well and truly stuck to 'ON'.

cheers again|||No probs :)

Dynamic Where clause

Hi
I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
clause
e.g
Where Value = myArray(0) or myArray(1)...
But the where clause can not be explicit because the length of the array is
variable
What is a professional way to do something like this?
ThksAre you just using T-SQL or are you using VB in the front end?
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:18A39651-A7B4-4208-9C52-515BC1198955@.microsoft.com...
> Hi
> I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
> clause
> e.g
> Where Value = myArray(0) or myArray(1)...
> But the where clause can not be explicit because the length of the array
> is
> variable
> What is a professional way to do something like this?
> Thks
>|||Check out this article on ASPFAQ to get started:
http://www.aspfaq.com/show.asp?id=2248
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:18A39651-A7B4-4208-9C52-515BC1198955@.microsoft.com...
> Hi
> I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
> clause
> e.g
> Where Value = myArray(0) or myArray(1)...
> But the where clause can not be explicit because the length of the array
> is
> variable
> What is a professional way to do something like this?
> Thks
>|||>> What is a professional way to do something like this? <<
Put the values in the only data structure allowed in SQL, a table
WHERE x IN (SELECT x FROM Parmlist
)|||Yeah the code is in VB, is not transac but I would like to know how to make
it too.|||On the VB.NET side, create the SQL Statement dynamically using a String
variable and a For Loop:
Dim sqlstr As String
sqlstr = "SELECT * FROM table1 WHERE col1 IN ("
For i = 0 To MyArray.Length - 1
sqlstr += CType(MyArray(i), String)
If i < MyArray.Length - 1 Then
sqlstr += ","
End If
Next
sqlstr += ")"
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:5897F0E6-FBE8-4BF4-97B4-DBD5A943DDB4@.microsoft.com...
> Yeah the code is in VB, is not transac but I would like to know how to
> make
> it too.|||pretty good thks
--
Kenny M.
"Michael C#" wrote:

> On the VB.NET side, create the SQL Statement dynamically using a String
> variable and a For Loop:
> Dim sqlstr As String
> sqlstr = "SELECT * FROM table1 WHERE col1 IN ("
> For i = 0 To MyArray.Length - 1
> sqlstr += CType(MyArray(i), String)
> If i < MyArray.Length - 1 Then
> sqlstr += ","
> End If
> Next
> sqlstr += ")"
>
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:5897F0E6-FBE8-4BF4-97B4-DBD5A943DDB4@.microsoft.com...
>
>

Dynamic WHERE clause

Hi
I have a WHERE clause, based on Params, passed in from a report, however, I
cannot get it to work properly.
I have to return two product codes, when a certain Types are passed in,
however, when it's anything else, I am only to return one product type.
Here's a sample of what I am trying to do:
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'CC', 'PO'
ELSE
'CC'
END)
Kind Regards
RickyTry
WHERE [Product Code] = 'CC' OR ( [Product Code] = 'PO' AND @.Type IN ('MD',
'BI') )
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:u$sWO8fjGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have a WHERE clause, based on Params, passed in from a report, however,
> I
> cannot get it to work properly.
> I have to return two product codes, when a certain Types are passed in,
> however, when it's anything else, I am only to return one product type.
> Here's a sample of what I am trying to do:
>
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'CC', 'PO'
> ELSE
> 'CC'
> END)
>
> Kind Regards
> Ricky
>|||No, you can't do this. You can try something like this though...
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'PO'
ELSE
'CC'
END, 'CC')
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi chaps
Thank you both for the suggestion, couldn't work this out, been stuck for a
few hours. I thought it would be a simple thing to do...anyway, thanks
again.
Kind Regards
Ricky
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:06AFD45A-62AB-49EE-AFED-F6095D3C0C2C@.microsoft.com...
> No, you can't do this. You can try something like this though...
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'PO'
> ELSE
> 'CC'
> END, 'CC')
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Dynamic WHERE Clause

Hi
I have a query which has a few different Time Period columns:
Half_Year (H1,H2)
Quarters (Q1,Q2,Q3,Q4)
Months (M1,M2,M3,... M12)
These periods are held in three difference columns.
I need to run this query with 2 params. One will be the year and other will
be one of the above three:
i.e
sp_Rating 2005, 'H1'
This is all transactions in months 1=6 for the year 2005.
or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
How can I dynamically interrogate the correct column, based on the param
supplied (H, Q, M) ?
Kind Regards
Ricky
(WIN2K,SQL2K-SP4)Ricky (ricky@.msn.com) writes:
> I have a query which has a few different Time Period columns:
> Half_Year (H1,H2)
> Quarters (Q1,Q2,Q3,Q4)
> Months (M1,M2,M3,... M12)
> These periods are held in three difference columns.
> I need to run this query with 2 params. One will be the year and other
> will be one of the above three:
> i.e
> sp_Rating 2005, 'H1'
> This is all transactions in months 1=6 for the year 2005.
> or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
> How can I dynamically interrogate the correct column, based on the param
> supplied (H, Q, M) ?
First of all, don't call your procedures sp_something. That prefix is
reserved for system procedures, and SQL Server first looks for a
procedure with such a name in the master database.
As for the question, I'm afraid that I dno't really understand. Does
this table has three columns? May then I ask the stupid question if
H1 can appear together with M7 to M12? I realise that if periodisation
takes place, this can happen. Then again, could a row really have
H1, Q3 and M10?
If you need all three columns try this:
SELECT ...
FROM tbl
WHERE (Halt_year = @.period AND @.period LIKE 'H%')
OR (Quarter = @.period AND @.period LIKE 'Q%')
OR (Months = @.period AND @.period LIKE 'M%')
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 have a query which has a few different Time Period columns: <<
Really? Mind showing us? Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.
Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
time in the universe. Try a proper design:
CREATE TABLE PeriodCalendar
(period_name CHAR(15) NOT NULL PRIMARY KEY
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL);
INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
etc. for all the possible fiscal, marketing and calendaral periods you
use.
Again, not a good design; you seem to think that a year should be an
integer, while the ANSI standard say it is a CHAR(4); get a copy of the
8601 standards, too. Now life a JOIN and a BETWEEN predicate.
And you have already been told about not using "sp_" prefixes because
they refer to where something is phycially located as well as having a
special meaning in SQL Server.|||Thanks guys for the reply, I'm ashamed to say that I have been using the
'sp_' prefix, I did no know that this was the case, I shall rename mine to
something else today/tomorrow.
Kind Regards
Ricky
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Really? Mind showing us? Please post DDL, so that people do not have
> to guess what the keys, constraints, Declarative Referential Integrity,
> data types, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications. It is very hard to debug code when
> you do not let us see it.
>
> Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
> time in the universe. Try a proper design:
> CREATE TABLE PeriodCalendar
> (period_name CHAR(15) NOT NULL PRIMARY KEY
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL);
> INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
> etc. for all the possible fiscal, marketing and calendaral periods you
> use.
>
other will be one of the above three: sp_Rating 2005, 'H1' <<
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.
> And you have already been told about not using "sp_" prefixes because
> they refer to where something is phycially located as well as having a
> special meaning in SQL Server.
>|||That's a mistake that we all make, until someone points out to us the
dangers.
In addition to what --CELKO-- posted, check out this article on calendar
tables, which goes into much more detail.
http://www.aspfaq.com/show.asp?id=2519
"ricky" <ricky@.ricky.com> wrote in message
news:O5wFxWHiGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks guys for the reply, I'm ashamed to say that I have been using the
> 'sp_' prefix, I did no know that this was the case, I shall rename mine to
> something else today/tomorrow.
> Kind Regards
> Ricky
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> other will be one of the above three: sp_Rating 2005, 'H1' <<
>|||Joe,
Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)?
I have always thought of it as an integer that one might add and subtract
from for various date functions (of course leap year can complicate it).
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.|||
--CELKO-- wrote:

>Again, not a good design; you seem to think that a year should be an
>integer, while the ANSI standard say it is a CHAR(4); get a copy of the
>8601 standards, too. Now life a JOIN and a BETWEEN predicate.
>
ANSI 8601 is a standard for the "representation" of dates and times.
It's not a standard for how to store them. If you think the ANSI standard
says that a year *is* a char(4), please quote the relevant section of the
standard.
My copy of the standard says only that "In *expressions* of calendar
dates, year is generally *represented* by four digits..." [emphasis mine]
Steve Kass
Drew University
http://www.stevekass.com|||>> Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)? <<
Mostly history. We based the SQL Standard on the pre-existing the
ISO-8601 Standards which is for display; it says nothing about internal
storage. We wanted to avoid anything to do with internal storage, like
specifying the use of numbers for dates.
You can see this with the specs for "EXTRACT(<temporal unit> FROM
<temporal exp)>" and the fact you have to use strings with "INTERVAL
<exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
and force a casting in certain places.
Of course, by the time we got something into SQL, each vendor had a
proprietary library and storage method which was exposed to the users.
Rats!!|||
--CELKO-- wrote:

>Mostly history. We based the SQL Standard on the pre-existing the
>ISO-8601 Standards which is for display; it says nothing about internal
>storage. We wanted to avoid anything to do with internal storage, like
>specifying the use of numbers for dates.
>You can see this with the specs for "EXTRACT(<temporal unit> FROM
><temporal exp)>" and the fact you have to use strings with "INTERVAL
><exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
>"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
>and force a casting in certain places.
>
The reason the <interval string> is a string is that its domain is not
<integer>. The domain includes '1-4', for example.
This has nothing whatsoever to do with the issue of whether ANSI 8601
says that
a "year is a char(4)" (it does not).
SK

>Of course, by the time we got something into SQL, each vendor had a
>proprietary library and storage method which was exposed to the users.
> Rats!!
>
>|||I apologise if I have caused a disagreement, but is there an issue, if I do
format YEAR as an INT?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uZknxeMiGHA.4080@.TK2MSFTNGP03.phx.gbl...
>
> --CELKO-- wrote:
>
an integer (in the standard)? <<
> The reason the <interval string> is a string is that its domain is not
> <integer>. The domain includes '1-4', for example.
> This has nothing whatsoever to do with the issue of whether ANSI 8601
> says that
> a "year is a char(4)" (it does not).
> SK
>

Friday, February 24, 2012

Dynamic Table Creation

Hi
I wish to create a table through sql code which picks up fields from
different tables in the database.
Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y'
and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'.
Is this possible? Any help is greatly appreciated. Thanks!
MJTry,
select a.w, a.x, b.y, b.z
into tablec
from tablaA as a inner join tableb as b on 0 = 1
This will not create constraints. You have to alter tablc and add them
manually.
AMB
"MJ" wrote:

> Hi
> I wish to create a table through sql code which picks up fields from
> different tables in the database.
> Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y'
> and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'.
> Is this possible? Any help is greatly appreciated. Thanks!
> MJ|||MJ,
In order to give a query for this, you need to provide the rule
that explains when to put TableA.W and TableA.X in the same
output row as TableB.Y and TableB.Z.
For example, say TableA is PeoplePhoneNumbers, and has columns
(Name,Phone), and TableB is CarLicenses with tables (VINnumber, License),
how do you match up name,phone pairs with VINnumber, license pairs?
Even if you don't care how things are matched up, you still need to decide
on a specific rule to follow.
Steve Kass
Drew University
MJ wrote:

>Hi
>I wish to create a table through sql code which picks up fields from
>different tables in the database.
>Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y'
>and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'.
>Is this possible? Any help is greatly appreciated. Thanks!
>MJ
>|||Hi yes using AMB's code it worked fine. I dont really care how they match
up and I think the constaint 0 = 1 allows for that.
Thanks both you guys!
MJ
"Steve Kass" wrote:

> MJ,
> In order to give a query for this, you need to provide the rule
> that explains when to put TableA.W and TableA.X in the same
> output row as TableB.Y and TableB.Z.
> For example, say TableA is PeoplePhoneNumbers, and has columns
> (Name,Phone), and TableB is CarLicenses with tables (VINnumber, License),
> how do you match up name,phone pairs with VINnumber, license pairs?
> Even if you don't care how things are matched up, you still need to deci
de
> on a specific rule to follow.
> Steve Kass
> Drew University
> MJ wrote:
>
>|||Columns are not fields; rows are not records. This is basic.
It sounds like you want to create a table on the fly, after the data
model is implemented. Surely not! That would mean that you have no
data model yet and should not have implemented a schema. You can
kludge it, but yu can also learn to be a good SQL programmer instead.|||Ah - I was under the assumption you wanted to put data into the
table, not just create an empty table. Alejandro's suggestion is
an excellent one for what you want.
SK
MJ wrote:
>Hi yes using AMB's code it worked fine. I dont really care how they match
>up and I think the constaint 0 = 1 allows for that.
>Thanks both you guys!
>MJ
>"Steve Kass" wrote:
>
>

Dynamic SQL Statement for Cursor

Hi
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>

Sunday, February 19, 2012

Dynamic SQL statements in a stored procedure

Hi

I have a small problem writing a stored procedure in a SQL Server 2000 database.

I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server dont generate a resulting SQL-statement.

Example:

CREATE PROCEDURE spDynStatement
AS

DECLARE @.sPartOfSQLStatement NVARCHAR(100)

-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .

-- As substitute I insert the string expression
SET @.sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''

-- SELECT @.sPartOfSQLStatement results in: '1' , '1.5'

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @.sPartOfSQLStatement ) -- does not work

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO

Thankfull for ideas on how to solve my problem,

PeterTry this

DECLARE @.sSQLStatement VARCHAR(4000)

SET @.sSQLStatement = 'SELECT * FROM BBNOrganization WHERE OrgStructureID IN(' + '''1''' + ', ' + '''1.5''' + ')'

EXEC @.sSQLStatement

Dynamic SQL Statement for Cursor

Hi
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>