Dynamic insert into table with datetime and int columns

Hello,
Me again..sorry about that!
Here is an excerpt from my stored procdure:
DECLARE
@.NbRecs int,
@.StartTime datetime,
@.EndTime datetime,
@.TableName varchar(50),
@.JobLog varchar(50),
@.DBName varchar(50)
@.SQL varchar(8000)
SET @.JobLog = @.DBName + '.dbo.DownloadJobLog'
IF OBJECT_ID(@.JobLog) IS NULL
BEGIN
SET @.SQL = 'CREATE TABLE ' + @.JobLog +
'(TableName varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
NbRecords int NOT NULL DEFAULT 0)
ON [PRIMARY]'
EXEC(@.SQL)
END
SET @.TableName = 'DOWNLOAD_START'
SET @.StartTime = CURRENT_TIMESTAMP
SET @.EndTime = CURRENT_TIMESTAMP
SET @.SQL = 'INSERT INTO ' + @.JobLog +
'VALUES(' + @.TableName + ', ' + @.StartTime + ', ' + @.EndTime +
', ' + @.Nbrecs + ')'
EXEC(@.SQL)
The create table portion works fine.
I get the following error message for the dynamic insert:
"Syntax error converting datetime from character
string."
Please help.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1First of all, don't do this. This is really poor practice. Post more specs
and we can help you find a much better solution.
That aside, there are a few things you can do to make your current hmm...
dynamic query work, yet pretty much all of them defy logic. The main problem
with this setup is that while your data is in correct data type you'll have
to mangle it by casting it as character data to build the query string. Ugh!
If you truly are in love with dynamic SQL consider using the sp_executesql
procedure. And please do some reading on the matter here:
http://www.sommarskog.se/dynamic_sql.html
ML|||Thanks for the reply. I had a feeling this was the type of response I was
going to get.
Being a newcomer, I'm still not quite sure why dynamic SQL seems to be such
a
"FAUX PAS" and why it is not recommended, but unfortunately I am confined to
this solution.
That being said, I can create the table using character data types instead o
f
datetime and int if it will
make things easier.
Also what is the advantage of using sp_executesql as opposed to EXEC(@.sql)?
I'm not opposed to sp_executesql and if you can provide an example I will us
e
it.
Briefly, my procedure reads the tables from a linked server(AS400 library),
copies the table structures, adds an identuty column to the new structure an
d
then copies the data to a database on the local server.
Hope this helps and will glady provide more info if necessary
ML wrote:
>First of all, don't do this. This is really poor practice. Post more specs
>and we can help you find a much better solution.
>That aside, there are a few things you can do to make your current hmm...
>dynamic query work, yet pretty much all of them defy logic. The main proble
m
>with this setup is that while your data is in correct data type you'll have
>to mangle it by casting it as character data to build the query string. Ugh
!
>If you truly are in love with dynamic SQL consider using the sp_executesql
>procedure. And please do some reading on the matter here:
>http://www.sommarskog.se/dynamic_sql.html
>ML
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||Think of a procedure as a system of pipelines that bring data from a source
outside a database into the database (and vice versa). Would you mix water
and oil in a regular pipeline? :)
sp_executesql will enable you to keep data in apporopriate data types, since
it supports parameters. There are good examples in Books Online:
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
Have you read Erland's article?
ML|||Cismail via webservertalk.com wrote:
> Thanks for the reply. I had a feeling this was the type of response I was
> going to get.
> Being a newcomer, I'm still not quite sure why dynamic SQL seems to be suc
h a
> "FAUX PAS" and why it is not recommended
Then you haven't understood how a tiered architecture works and why we
use client-server databases at all. Nor have you understood why we use
stored procedures. Erland Sommarskog's article in the posted link is
essential reading on dynamic SQL.
I don't understand why you are "confined to this solution" when in your
example the only dynamic code you want to parameterize is the table
name (the rest can be done without dynamic code). Why wouldn't you know
the name(s) of your table(s) at design time? It seems you are creating
tables "on the fly" which is a great way to build a system that will
totally fail in terms of scalability and manageability.
If you genuinely know nothing about the structure of your data source
until runtime then you are probably using the wrong tool. Take a look
at some 3rd party data integration software product that will manage
the metadata for you.
David Portas
SQL Server MVP
--|||I will definately take both your advice and read the article.
In the meantime, perhaps someone can provide a better solution to my problem
.
...
I need to download a bunch(the exact number is irrelevant and it can change
at any time) of files
from the AS400 onto our server. How would you solve this......
David Portas wrote:
>Then you haven't understood how a tiered architecture works and why we
>use client-server databases at all. Nor have you understood why we use
>stored procedures. Erland Sommarskog's article in the posted link is
>essential reading on dynamic SQL.
>I don't understand why you are "confined to this solution" when in your
>example the only dynamic code you want to parameterize is the table
>name (the rest can be done without dynamic code). Why wouldn't you know
>the name(s) of your table(s) at design time? It seems you are creating
>tables "on the fly" which is a great way to build a system that will
>totally fail in terms of scalability and manageability.
>If you genuinely know nothing about the structure of your data source
>until runtime then you are probably using the wrong tool. Take a look
>at some 3rd party data integration software product that will manage
>the metadata for you.
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||Cismail via webservertalk.com wrote:
> I need to download a bunch(the exact number is irrelevant and it can chang
e
> at any time) of files
> from the AS400 onto our server. How would you solve this......
>
That would depend on just what the interface is and what the
requirements are. Actually "downloading a bunch of files" isn't really
a SQL Server task. I'll assume that what you really want is to
integrate them into some SQL Server database. You say that the number
of files can change but I'll assume that these files are all of the
same or similar structure - or at least of a structure that you can
determine in advance.
As far as the interface goes, the main options are to download data as
files or to make it available online via an ODBC or OLEDB interface -
as a linked server for example.
One option for actually loading the data is to use a "staging" table.
By that I mean you have a SQL table that approximates the source data
structure. The data is appended to the staging table (maybe with a file
name and line number as additional columns) and then manipulated using
SQL statements to integrate it into your target relational database
schema. This is sometimes called the "ELT" approach: Extract - Load -
Transform.
Another method is "ETL" (Extract - Transform - Load). This usually
involves implementing the Transformation phase in some software tool
outside the database. There are a variety of data integration tools
that will accomplish this for you. If you are downloading raw EBCDIC
files from an AS400 system you will probably need to use such software
to convert them. ETL software tools are particularly useful if the
source structure changes often because the software can maintain its
own database of metadata and validation rules and you may be able to
monitor and change these with a minimum of coding and maintenance.
David Portas
SQL Server MVP
--|||Cismail via webservertalk.com (u14416@.uwe) writes:
> SET @.TableName = 'DOWNLOAD_START'
> SET @.StartTime = CURRENT_TIMESTAMP
> SET @.EndTime = CURRENT_TIMESTAMP
> SET @.SQL = 'INSERT INTO ' + @.JobLog +
> 'VALUES(' + @.TableName + ', ' + @.StartTime + ', ' + @.EndTime +
> ', ' + @.Nbrecs + ')'
> EXEC(@.SQL)
> The create table portion works fine.
> I get the following error message for the dynamic insert:
> "Syntax error converting datetime from character
> string."
You need to convert the datetime values to string to interpolate them
in the string. This is certainly messy. You meed
SELECT @.StartTimeStr = convert(varchar(23), @.Starttime, 126)
And then you must remember to quote them. Using sp_executesql is a lot
easier:
SET @.SQL = 'INSERT INTO ' + @.JobLog +
'VALUES(@.TableName, @.StartTime, @.EndTime, @.Nbrecs )'
SELET @.params = N'@.TableName varchar(50), @.StartTime datetime, ' +
N'@.EndTime datetime, @.NbRecords int'
EXEC sp_executesql @.SQL, @.params, @.TableName, @.Starttime, @.EndTime,
@.NbRecords
As I can read out from the script, the only thing that's really dynamic
here is the database name?
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

Dynamic Cross Tab Using Cursors.

I have two tables
1)Rollout_detail
start_date Datetime,
contract_date Datetime,
budget_amt Money
store_id int(foriegn key referring store.store_id)
pan_number varchar(20)
roll_id int

2)store
store_id int(primary key)
skey varchar(10)

these two tables are tied with store_id
and in rollout_detail there can be many pan_numbers for a given
store.(pan_number + store ) are unique.

Now here is the problem.

I need to generate a cross tab report with
store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
pan2_Contract_date,pan2_budget_amt,pan2_start_date and so on.

I tried this with in a procedure.
------------------
CREATE PROCEDURE crosstab
@.roll_id INT
WITH ENCRYPTION
AS
DECLARE @.sql VARCHAR(8000),@.panNumber VARCHAR(20)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
FROM rollout_detail WHERE roll_id=@.roll_id
OPEN al_cursor
SELECT @.sql='SELECT skey, '
FETCH NEXT FROM al_cursor INTO @.panNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.sql=@.sql+'(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN start_date END) as
P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN budget_amt END) as
P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN contract_date END) as
P'+rtrim(@.panNumber)+'_cd,'

FETCH NEXT FROM al_cursor INTO @.panNumber
END
SELECT @.sql=left(@.sql, len(@.sql)-1)+' '
SELECT @.sql=@.sql+'FROM rollout_detail rd,store s
WHERE rd.store_id=s.store_id AND roll_id='+cast(@.roll_id as varchar)

EXEC (@.sql)
CLOSE al_cursor
DEALLOCATE al_cursor
SET ANSI_WARNINGS ON

EXECUTE crosstab 1
------------------
I am getting multiple records for the same store
the result set is.

skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
1 12/2/04 400.0 3/4/05 NULL NULL NULL
1 NULL NULL NULL 5/6/04 566.00 3/4/04

I want the result set merged for each store.

Please help,

Thanks a million

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Jaidev Paruchuri" <jaip26@.hotmail.com> wrote in message
news:408fbf3d$0$200$75868355@.news.frii.net...
> I have two tables
> 1)Rollout_detail
> start_date Datetime,
> contract_date Datetime,
> budget_amt Money
> store_id int(foriegn key referring store.store_id)
> pan_number varchar(20)
> roll_id int
> 2)store
> store_id int(primary key)
> skey varchar(10)
> these two tables are tied with store_id
> and in rollout_detail there can be many pan_numbers for a given
> store.(pan_number + store ) are unique.
> Now here is the problem.
> I need to generate a cross tab report with
> store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
> pan2_Contract_date,pan2_budget_amt,pan2_start_date and so on.
> I tried this with in a procedure.
> ------------------
> CREATE PROCEDURE crosstab
> @.roll_id INT
> WITH ENCRYPTION
> AS
> DECLARE @.sql VARCHAR(8000),@.panNumber VARCHAR(20)
> SET NOCOUNT ON
> SET ANSI_WARNINGS OFF
> DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
> FROM rollout_detail WHERE roll_id=@.roll_id
> OPEN al_cursor
> SELECT @.sql='SELECT skey, '
> FETCH NEXT FROM al_cursor INTO @.panNumber
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.sql=@.sql+'(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN start_date END) as
> P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN budget_amt END) as
> P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN contract_date END) as
> P'+rtrim(@.panNumber)+'_cd,'
> FETCH NEXT FROM al_cursor INTO @.panNumber
> END
> SELECT @.sql=left(@.sql, len(@.sql)-1)+' '
> SELECT @.sql=@.sql+'FROM rollout_detail rd,store s
> WHERE rd.store_id=s.store_id AND roll_id='+cast(@.roll_id as varchar)
> EXEC (@.sql)
> CLOSE al_cursor
> DEALLOCATE al_cursor
> SET ANSI_WARNINGS ON
> EXECUTE crosstab 1
> ------------------
> I am getting multiple records for the same store
> the result set is.
> skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
> 1 12/2/04 400.0 3/4/05 NULL NULL NULL
> 1 NULL NULL NULL 5/6/04 566.00 3/4/04
>
> I want the result set merged for each store.
> Please help,
> Thanks a million
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You're probably looking for something like this - using MAX() and GROUP BY:

select skey,
max(case when ...) as A,
max(case when ...) as B,
...
from
...
where
...
group by skey

Simon|||Simon,

That worked.
Thankyou very much for the solution.
Though it was simple,I was thinking about alternate solutions.I really
appreaciate you guys.

--Jay

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

dynamic columns

Hi all, a quicky.

What is wrong with:

CREATE PROCEDURE dbo.QuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int
declare @.price as smallint
declare @.daycolumn as nvarchar(6)

set @.price = 0

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

set @.price = (SELECT @.daycolumn FROM pasPriceTable)

return @.price
GO

Problem is:
Syntax error converting the nvarchar value 'Day_10' to a column of data type smallint

How do I set a variable to a result from a query?
Thanks,
/ j0rgeYou can't use a variable to reference a column name...
You need to build a string and then eval the string... see below.

BTW, you'll want to put a where clause in to be sure you return a single value for price..

You should look into using sp_executesql as well...

Hope this helps...
jzapp
--------------------
CREATE PROCEDURE dbo.QuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int

declare @.daycolumn as nvarchar(6)

declare @.str varchar(500)

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

SET @.str = '
declare @.price as smallint
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)
return @.price
'
EXEC(@.str)

GO|||You can't use a variable to reference a column name...
YES YOU CAN. This works:

CREATE PROCEDURE dbo.pasQuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int
declare @.price as smallint
declare @.daycolumn as nvarchar(6)

set @.price = 0

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

SELECT @.daycolumn FROM pasPriceTable
GO

BUT the result gets returned as a record not as a value with return which is what I need.
/ jorge

Originally posted by jzapp
You can't use a variable to reference a column name...
You need to build a string and then eval the string... see below.

BTW, you'll want to put a where clause in to be sure you return a single value for price..

You should look into using sp_executesql as well...

Hope this helps...
jzapp
--------------------
CREATE PROCEDURE dbo.QuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int

declare @.daycolumn as nvarchar(6)

declare @.str varchar(500)

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

SET @.str = '
declare @.price as smallint
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)
return @.price
'
EXEC(@.str)

GO|||When you select a variable such as...
SELECT @.colname FROM table, what you get is the actual string value of that variable for every record in the table... and not the data value of the field.
It's equivalent to...
SELECT 'Day_10' FROM table (SQL interprets Day_10 as a string and not as a fieldname)
Your recordset is probably...
Day_10
Day_10
Day_10
.
.
.

Also, as I stated before, unless you know that this query will only return one record, you will need to put in a WHERE clause..

regards...|||Weird, i swar it worked somehow before.
Thanks.
/ jorge

Originally posted by jzapp
When you select a variable such as...
SELECT @.colname FROM table, what you get is the actual string value of that variable for every record in the table... and not the data value of the field.
It's equivalent to...
SELECT 'Day_10' FROM table (SQL interprets Day_10 as a string and not as a fieldname)
Your recordset is probably...
Day_10
Day_10
Day_10
.
.
.

Also, as I stated before, unless you know that this query will only return one record, you will need to put in a WHERE clause..

regards...|||Oh no, trouble running the EXEC version.
A RETURN statement with a return value cannot be used in this context.
/ jorge

Originally posted by j0rge
Weird, i swar it worked somehow before.
Thanks.
/ jorge|||rats...

How about
SELECT @.price AS Price|||Nope.. same thing.

See the problem is I need a return value and returns cannot be a part of a EXEC statement.

BUT if there is a @.@. command to get the last run query then I could:

set @.price = (select price from @.@.lastrunstatementrowone)
return @.price

I've battled with this for a while now.

Maybe there is another way to make the table.
I have a specific amount of days which have one price for a certain period.

that is:
Valid from 12/2003 to 12/2004
Day1=20
Day2=25
...
Day40=240

There can be 'overrides' for specific dates and days.
Valid from 04/2004 to 05/2004
Day20=60
Day21=60

Any ideas? instead of running dynamic SQL?
/ jorge


Originally posted by jzapp
rats...

How about
SELECT @.price AS Price|||Just off the top of my head you could do something like this...
(I haven't tested this code)

SET @.str = '.....
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)

CREATE TABLE #temptable (
ColA [int] IDENTITY (1, 1) NOT NULL,
ColB varchar(50) NOT NULL
)

INSERT INTO #temptable (ColB) VALUES ( @.price )
'

EXEC(@.str)

declare @.p varchar(20)
SELECT @.p = ColB FROM #temptable WHERE ColA = @.@.identity
return @.p|||This code will run A LOT. I want it to be simple, and there must be a way of querying the 'last ran statement'. NO?

Thanks for you help.
/ jorge

Originally posted by jzapp
Just off the top of my head you could do something like this...
(I haven't tested this code)

SET @.str = '.....
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)

CREATE TABLE #temptable (
ColA [int] IDENTITY (1, 1) NOT NULL,
ColB varchar(50) NOT NULL
)

INSERT INTO #temptable (ColB) VALUES ( @.price )
'

EXEC(@.str)

declare @.p varchar(20)
SELECT @.p = ColB FROM #temptable WHERE ColA = @.@.identity
return @.p