Ecommerce database design?

I am setting up a simple store to sell a few products and need some input on
how to design the database for the best efficiency.
Many but not all of the products I sell will have variants. For example, I
will be selling photo prints of different sizes - 5x7, 11x14, etc. , and
packs of cards in varying quantities. They are just different sizes of the
same product, so I don't really want to set each one up as a separate
product record.
However, each size will be a different price so that has to be designed for
too.
Can you direct me to an example of the best way to set up tables to allow
for such a project?
Thanks,
PaulPaul
I think the 'proper' design would call for two tables. One
for the base products, this would have a product-type ie
photo prints, product-id and any other data that was
common to all versions of the product (maybe a foreign key
to a supplier table).
The second table would be the product version table, where
you would have a row for every different version of the
product.
I say 'proper' design because depending on the amount of
data items in each table and the volumes you are dealing
with you may find your system runs better if you de-
normalise the table and make it just one table anyway.
Hope this helps. (And does not confuse.)
Regards
John

Dynamically creating variable names

Ok, here it goes. I'll try and explain my situation as best as I can.
I would like to dynamically create variable names. We store our data in
monthly partitioned tables.......table_200501, table_200502...
I am creating a view that is a union of all of the partitioned tables. So
in order to do this I am creating a cursor that selects all tables from
information_schema where table_name like 'table_%'
Then once I have the table name I am creating a varchar that does my select
from the table that is passed into the cursor variable. In the end I will
have a varchar that looks like this
select....................from table_200501
union all
select...................from table_200502
Over time the varchar that I have created will run out of space since it can
only hold 8000 characters. So once the size of the varchar gets near 8000 I
would like to create a new variable named sqlstringn.........with n being
the next number available.
So just for sample I tried doing this but it wont work
declare @.counter int
set @.counter = 5
declare @.SQLString + @.counter as varchar(8000)
Obviously this doesn't work, but is there any other way to dynamically
create variable names?
Any help or suggestions are very much appreciated.
ThanksI did that once (dynamically creating variables), and it required creating
dynamic SQL within dynamic SQL.
You probably don't want that in a production application.
What you can do is to allocate, say three, variables and concatenate them on
execution. (ie: Execute (@.Var1 + @.Var2 + @.Var3) I know you are doing this
in a cursor, but the techinque should be the same.
I don't mean to stray, but is it possible to modify your design from
horizontal to vertical so you don't have to deal with so many tables?
"Andy" wrote:

> Ok, here it goes. I'll try and explain my situation as best as I can.
> I would like to dynamically create variable names. We store our data in
> monthly partitioned tables.......table_200501, table_200502...
> I am creating a view that is a union of all of the partitioned tables. So
> in order to do this I am creating a cursor that selects all tables from
> information_schema where table_name like 'table_%'
> Then once I have the table name I am creating a varchar that does my selec
t
> from the table that is passed into the cursor variable. In the end I will
> have a varchar that looks like this
> select....................from table_200501
> union all
> select...................from table_200502
> Over time the varchar that I have created will run out of space since it c
an
> only hold 8000 characters. So once the size of the varchar gets near 8000
I
> would like to create a new variable named sqlstringn.........with n bei
ng
> the next number available.
> So just for sample I tried doing this but it wont work
> declare @.counter int
> set @.counter = 5
> declare @.SQLString + @.counter as varchar(8000)
> Obviously this doesn't work, but is there any other way to dynamically
> create variable names?
> Any help or suggestions are very much appreciated.
> Thanks
>

Dynamically adding fields to a report.

Not sure how to tackle this one. I need to create a report that dynamically
adds fields based on information from a data store.
[Abstracting the problem for clarity]
Let's say I have a table with contact data (FirstName, LastName, Street,
City, State, Zip) and a customer table with a definition of what contact
fields they use and how to render them.
I need a report that I pass a customerId and it looks up what fields to
pull, say customer1's report would look like:
FirstName LastName
Zip
And customer2's report would look like
FullName (Combining First and LastName)
Street
City, Zip
Each report needs to only show the relavant data, and additionlly position
it according the cutomer's defined format. Creating a report for each
customer is out of the question, way too many customers, and that's just
retarded. I thought about adding every field to the report and hiding the
ones that weren't used but positioning would be a headache.
Is there a way that I can dynamically create and add fields to the report at
runtime?
Any ideas would be greatly appriciated.Sorry, dupe of a previous submit. See Below.|||The short answer is no, since dynamically adding report elements is not
supported in version 1.0 of Reporting Services. That said, it looks like the
best workaround in your case is to pre-process the report by loading the
report definition in XML DOM and add/remove the fields you don't need. This
will require an application front-end to generate the report definition,
upload and generate the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> Not sure how to tackle this one. I need to create a report that
dynamically
> adds fields based on information from a data store.
> [Abstracting the problem for clarity]
> Let's say I have a table with contact data (FirstName, LastName, Street,
> City, State, Zip) and a customer table with a definition of what contact
> fields they use and how to render them.
> I need a report that I pass a customerId and it looks up what fields to
> pull, say customer1's report would look like:
> FirstName LastName
> Zip
> And customer2's report would look like
> FullName (Combining First and LastName)
> Street
> City, Zip
> Each report needs to only show the relavant data, and additionlly position
> it according the cutomer's defined format. Creating a report for each
> customer is out of the question, way too many customers, and that's just
> retarded. I thought about adding every field to the report and hiding the
> ones that weren't used but positioning would be a headache.
> Is there a way that I can dynamically create and add fields to the report
at
> runtime?
> Any ideas would be greatly appriciated.|||You need something like ad-hoc right?
It will be available with SQL 2005.
But right now you can dynamicly build query in SQL. I did it once. it
is not perfect report but idea is :
1. create parameter string where user will put field they want to see
using coma delimeter
2. pass this string on SP and parse it into the table.
generate query what return you what ever you need just remember each
field should be name generic (col1, col2, col3) and sequence should be
how they want to see it on the screen
3. create report base on col1, col2 col3 data returned.
If you need more info please send me email(natta@.netzero.net). I will
try to send you example.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message news:<#KjqSlqzEHA.1564@.TK2MSFTNGP09.phx.gbl>...
> The short answer is no, since dynamically adding report elements is not
> supported in version 1.0 of Reporting Services. That said, it looks like the
> best workaround in your case is to pre-process the report by loading the
> report definition in XML DOM and add/remove the fields you don't need. This
> will require an application front-end to generate the report definition,
> upload and generate the report.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
> news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> > Not sure how to tackle this one. I need to create a report that
> dynamically
> > adds fields based on information from a data store.
> >
> > [Abstracting the problem for clarity]
> > Let's say I have a table with contact data (FirstName, LastName, Street,
> > City, State, Zip) and a customer table with a definition of what contact
> > fields they use and how to render them.
> >
> > I need a report that I pass a customerId and it looks up what fields to
> > pull, say customer1's report would look like:
> > FirstName LastName
> > Zip
> >
> > And customer2's report would look like
> > FullName (Combining First and LastName)
> > Street
> > City, Zip
> >
> > Each report needs to only show the relavant data, and additionlly position
> > it according the cutomer's defined format. Creating a report for each
> > customer is out of the question, way too many customers, and that's just
> > retarded. I thought about adding every field to the report and hiding the
> > ones that weren't used but positioning would be a headache.
> >
> > Is there a way that I can dynamically create and add fields to the report
> at
> > runtime?
> >
> > Any ideas would be greatly appriciated.

Dynamic Sql to update a table row

Hi All MSSQL Experts-

I am facing a typical problem in one of my store procedure.

I am trying to update a table thru a dynamically generated SQL statement.

In my stored procedure I do all kinds of looping and manipultion to get this statement and execute like this:

mSQL = 'UPDATE TblSomeTable SET FldSomeFile=200 WHERE FldPK=100'
EXEC (mSQL)

This stuff has always worked for me, for Temporary tables, but some how does not seem to work on physical table. Am I doing something wrong? Missing something?

Any help will be greatly appreciated.

Best Wishes,Hi All-

Please never mind, the problem was with my field being int and the value I was trying to set it to was somethnig like 0.233, which eventually kept putting in there...

Regards

Dynamic SQL another example

In several messages that I post early I need help for create Dynamic SQL or
that I trying to do.
Here is an example of my store procedure
ALTER PROCEDURE dbo.Test
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
SET LANGUAGE us_english
SET NOCOUNT ON
declare @.Colum sysname
declare @.MyDate nvarchar(10)
set @.MyDate = '2004-05-05'
set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
print '---'
print 'The following expression no generate an error but not return me'
print 'anything rows. ( Both are String )'
print '---'
-- Both are String
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
print '================================================= ==============='
print
'----'
print 'Thi following expression generate an errorto have types mismatch).'
print
'----'
-- To have types mismatch.
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
print '================================================= ==============='
SET NOCOUNT OFF
RETURN
-- The out --
Running dbo."Test".
The following expression no generate an error but not return me
anything rows. ( Both are String )
Expression:
@.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
Result:
FechaMie>=2004-05-05
================================================== ==============
Thi following expression generate an errorto have types mismatch).
Expression:
@.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
Result:
Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running dbo."Test".
I just posted a reply to this. No need to repost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In several messages that I post early I need help for create Dynamic SQL or
> that I trying to do.
> Here is an example of my store procedure
> ALTER PROCEDURE dbo.Test
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
> SET LANGUAGE us_english
> SET NOCOUNT ON
> declare @.Colum sysname
> declare @.MyDate nvarchar(10)
> set @.MyDate = '2004-05-05'
> set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> print '---'
> print 'The following expression no generate an error but not return me'
> print 'anything rows. ( Both are String )'
> print '---'
> -- Both are String
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> print '================================================= ==============='
> print
> '----'
> print 'Thi following expression generate an errorto have types mismatch).'
> print
> '----'
> -- To have types mismatch.
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> print '================================================= ==============='
> SET NOCOUNT OFF
> RETURN
>
> -- The out --
>
> Running dbo."Test".
> The following expression no generate an error but not return me
> anything rows. ( Both are String )
> Expression:
> @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> Result:
> FechaMie>=2004-05-05
> ================================================== ==============
>
> ----
> Thi following expression generate an errorto have types mismatch).
> ----
> Expression:
> @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> Result:
> Syntax error converting datetime from character string.
> No rows affected.
> (0 row(s) returned)
> @.RETURN_VALUE =
> Finished running dbo."Test".
>
>
|||The following code represents my problem. It's using Northwind database. You
can copy and paste in the SQL analyzer directly and run so you can see the
problem.
My question is: How Can I build a SQL statement but doing replacement of
certain values on fly.
The causes are error data mismatch.
NOTE: I try sp_executeslq and this entire thing.
ALTER PROCEDURE dbo.StoredProcedure1
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
set language us_English
set nocount on
declare @.BirthDatePos int
declare @.colum sysname -- sysname is equal to nvarchar(128)
/*
* ----
* When @.DateFrom and @.DateTo are datetime the SQL-Server
* not run. I get the following error message:
* Syntax error converting datetime from character string.
* ----
*/
declare @.DateFrom datetime
declare @.DateTo datetime
/*
* ---
* When @.DateFrom and @.DateTo are both characters the SQL-Server
* run but not return anything.
* ---
*/
--declare @.DateFrom nvarchar(8)
--declare @.DateTo nvarchar(8)
/* Creating my Dynamic SQL: */
set @.DateFrom = '20040505'
set @.DateTo = '20040505'
set @.BirthDatePos = 6
set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
SELECT BirthDate
FROM Employees
WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
set nocount off
RETURN
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> I just posted a reply to this. No need to repost.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
or[vbcol=seagreen]
'----'[vbcol=seagreen]
mismatch).'[vbcol=seagreen]
'----'
>
|||I posted a reply to your other thread about 10 minutes ago.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
> The following code represents my problem. It's using Northwind database. You
> can copy and paste in the SQL analyzer directly and run so you can see the
> problem.
>
> My question is: How Can I build a SQL statement but doing replacement of
> certain values on fly.
>
> The causes are error data mismatch.
>
> NOTE: I try sp_executeslq and this entire thing.
>
>
> ALTER PROCEDURE dbo.StoredProcedure1
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
>
> set language us_English
> set nocount on
>
> declare @.BirthDatePos int
> declare @.colum sysname -- sysname is equal to nvarchar(128)
>
> /*
> * ----
> * When @.DateFrom and @.DateTo are datetime the SQL-Server
> * not run. I get the following error message:
> * Syntax error converting datetime from character string.
> * ----
> */
> declare @.DateFrom datetime
> declare @.DateTo datetime
>
> /*
> * ---
> * When @.DateFrom and @.DateTo are both characters the SQL-Server
> * run but not return anything.
> * ---
> */
> --declare @.DateFrom nvarchar(8)
> --declare @.DateTo nvarchar(8)
>
> /* Creating my Dynamic SQL: */
> set @.DateFrom = '20040505'
> set @.DateTo = '20040505'
> set @.BirthDatePos = 6
> set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
>
> SELECT BirthDate
> FROM Employees
> WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> set nocount off
> RETURN
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> or
> '----'
> mismatch).'
> '----'
>
|||Mr. Tibor You code was enough clear I want give thanks to you publicly.
yours advisor was profit.
I resolve my problem....!
MArio
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#I2FIrfMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> I posted a reply to your other thread about 10 minutes ago.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
You[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
SQL[vbcol=seagreen]
'---'[vbcol=seagreen]
me'[vbcol=seagreen]
'---'[vbcol=seagreen]
'================================================= ==============='[vbcol=seagreen]
'----'[vbcol=seagreen]
'----'[vbcol=seagreen]
'================================================= ==============='
> ----
> ----
>

Dynamic SQL another example

In several messages that I post early I need help for create Dynamic SQL or
that I trying to do.
Here is an example of my store procedure
ALTER PROCEDURE dbo.Test
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
SET LANGUAGE us_english
SET NOCOUNT ON
declare @.Colum sysname
declare @.MyDate nvarchar(10)
set @.MyDate = '2004-05-05'
set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
print '---'
print 'The following expression no generate an error but not return me'
print 'anything rows. ( Both are String )'
print '---'
-- Both are String
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
print '================================================================'
print
'----'
print 'Thi following expression generate an error:(to have types mismatch).'
print
'----'
-- To have types mismatch.
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
print '================================================================'
SET NOCOUNT OFF
RETURN
-- The out --
Running dbo."Test".
---
The following expression no generate an error but not return me
anything rows. ( Both are String )
---
Expression:
@.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
Result:
FechaMie>=2004-05-05
================================================================
----
Thi following expression generate an error:(to have types mismatch).
----
Expression:
@.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
Result:
Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = Finished running dbo."Test".I just posted a reply to this. No need to repost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In several messages that I post early I need help for create Dynamic SQL or
> that I trying to do.
> Here is an example of my store procedure
> ALTER PROCEDURE dbo.Test
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
> SET LANGUAGE us_english
> SET NOCOUNT ON
> declare @.Colum sysname
> declare @.MyDate nvarchar(10)
> set @.MyDate = '2004-05-05'
> set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> print '---'
> print 'The following expression no generate an error but not return me'
> print 'anything rows. ( Both are String )'
> print '---'
> -- Both are String
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> print '================================================================'
> print
> '----'
> print 'Thi following expression generate an error:(to have types mismatch).'
> print
> '----'
> -- To have types mismatch.
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> print '================================================================'
> SET NOCOUNT OFF
> RETURN
>
> -- The out --
>
> Running dbo."Test".
> ---
> The following expression no generate an error but not return me
> anything rows. ( Both are String )
> ---
> Expression:
> @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> Result:
> FechaMie>=2004-05-05
> ================================================================>
> ----
> Thi following expression generate an error:(to have types mismatch).
> ----
> Expression:
> @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> Result:
> Syntax error converting datetime from character string.
> No rows affected.
> (0 row(s) returned)
> @.RETURN_VALUE => Finished running dbo."Test".
>
>|||The following code represents my problem. It's using Northwind database. You
can copy and paste in the SQL analyzer directly and run so you can see the
problem.
My question is: How Can I build a SQL statement but doing replacement of
certain values on fly.
The causes are error data mismatch.
NOTE: I try sp_executeslq and this entire thing.
ALTER PROCEDURE dbo.StoredProcedure1
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
set language us_English
set nocount on
declare @.BirthDatePos int
declare @.colum sysname -- sysname is equal to nvarchar(128)
/*
* ----
* When @.DateFrom and @.DateTo are datetime the SQL-Server
* not run. I get the following error message:
* Syntax error converting datetime from character string.
* ----
*/
declare @.DateFrom datetime
declare @.DateTo datetime
/*
* ---
* When @.DateFrom and @.DateTo are both characters the SQL-Server
* run but not return anything.
* ---
*/
--declare @.DateFrom nvarchar(8)
--declare @.DateTo nvarchar(8)
/* Creating my Dynamic SQL: */
set @.DateFrom = '20040505'
set @.DateTo = '20040505'
set @.BirthDatePos = 6
set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
SELECT BirthDate
FROM Employees
WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
set nocount off
RETURN
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> I just posted a reply to this. No need to repost.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > In several messages that I post early I need help for create Dynamic SQL
or
> > that I trying to do.
> >
> > Here is an example of my store procedure
> >
> > ALTER PROCEDURE dbo.Test
> > /*
> > (
> > @.parameter1 datatype = default value,
> > @.parameter2 datatype OUTPUT
> > )
> > */
> > AS
> >
> > SET LANGUAGE us_english
> > SET NOCOUNT ON
> >
> > declare @.Colum sysname
> > declare @.MyDate nvarchar(10)
> >
> > set @.MyDate = '2004-05-05'
> > set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> >
> > print '---'
> > print 'The following expression no generate an error but not return me'
> > print 'anything rows. ( Both are String )'
> > print '---'
> >
> > -- Both are String
> > print 'Expression:'
> > print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> > print 'Result:'
> > print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> >
> > print '================================================================'
> >
> > print
> >
'----'
> > print 'Thi following expression generate an error:(to have types
mismatch).'
> > print
> >
'----'
> >
> > -- To have types mismatch.
> > print 'Expression:'
> > print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> > print 'Result:'
> > print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> >
> > print '================================================================'
> >
> > SET NOCOUNT OFF
> > RETURN
> >
> >
> > -- The out --
> >
> >
> > Running dbo."Test".
> >
> > ---
> > The following expression no generate an error but not return me
> > anything rows. ( Both are String )
> > ---
> > Expression:
> > @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > Result:
> > FechaMie>=2004-05-05
> > ================================================================> >
> >
> >
> > ----
> > Thi following expression generate an error:(to have types mismatch).
> > ----
> > Expression:
> > @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > Result:
> > Syntax error converting datetime from character string.
> > No rows affected.
> > (0 row(s) returned)
> > @.RETURN_VALUE => > Finished running dbo."Test".
> >
> >
> >
>|||I posted a reply to your other thread about 10 minutes ago.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
> The following code represents my problem. It's using Northwind database. You
> can copy and paste in the SQL analyzer directly and run so you can see the
> problem.
>
> My question is: How Can I build a SQL statement but doing replacement of
> certain values on fly.
>
> The causes are error data mismatch.
>
> NOTE: I try sp_executeslq and this entire thing.
>
>
> ALTER PROCEDURE dbo.StoredProcedure1
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
>
> set language us_English
> set nocount on
>
> declare @.BirthDatePos int
> declare @.colum sysname -- sysname is equal to nvarchar(128)
>
> /*
> * ----
> * When @.DateFrom and @.DateTo are datetime the SQL-Server
> * not run. I get the following error message:
> * Syntax error converting datetime from character string.
> * ----
> */
> declare @.DateFrom datetime
> declare @.DateTo datetime
>
> /*
> * ---
> * When @.DateFrom and @.DateTo are both characters the SQL-Server
> * run but not return anything.
> * ---
> */
> --declare @.DateFrom nvarchar(8)
> --declare @.DateTo nvarchar(8)
>
> /* Creating my Dynamic SQL: */
> set @.DateFrom = '20040505'
> set @.DateTo = '20040505'
> set @.BirthDatePos = 6
> set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
>
> SELECT BirthDate
> FROM Employees
> WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> set nocount off
> RETURN
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> > I just posted a reply to this. No need to repost.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Mario Reiley" <mreiley@.cantv.net> wrote in message
> news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > > In several messages that I post early I need help for create Dynamic SQL
> or
> > > that I trying to do.
> > >
> > > Here is an example of my store procedure
> > >
> > > ALTER PROCEDURE dbo.Test
> > > /*
> > > (
> > > @.parameter1 datatype = default value,
> > > @.parameter2 datatype OUTPUT
> > > )
> > > */
> > > AS
> > >
> > > SET LANGUAGE us_english
> > > SET NOCOUNT ON
> > >
> > > declare @.Colum sysname
> > > declare @.MyDate nvarchar(10)
> > >
> > > set @.MyDate = '2004-05-05'
> > > set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> > >
> > > print '---'
> > > print 'The following expression no generate an error but not return me'
> > > print 'anything rows. ( Both are String )'
> > > print '---'
> > >
> > > -- Both are String
> > > print 'Expression:'
> > > print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> > > print 'Result:'
> > > print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > >
> > > print '================================================================'
> > >
> > > print
> > >
> '----'
> > > print 'Thi following expression generate an error:(to have types
> mismatch).'
> > > print
> > >
> '----'
> > >
> > > -- To have types mismatch.
> > > print 'Expression:'
> > > print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> > > print 'Result:'
> > > print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > >
> > > print '================================================================'
> > >
> > > SET NOCOUNT OFF
> > > RETURN
> > >
> > >
> > > -- The out --
> > >
> > >
> > > Running dbo."Test".
> > >
> > > ---
> > > The following expression no generate an error but not return me
> > > anything rows. ( Both are String )
> > > ---
> > > Expression:
> > > @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > > Result:
> > > FechaMie>=2004-05-05
> > > ================================================================> > >
> > >
> > >
> > > ----
> > > Thi following expression generate an error:(to have types mismatch).
> > > ----
> > > Expression:
> > > @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > > Result:
> > > Syntax error converting datetime from character string.
> > > No rows affected.
> > > (0 row(s) returned)
> > > @.RETURN_VALUE => > > Finished running dbo."Test".
> > >
> > >
> > >
> >
> >
>|||Mr. Tibor You code was enough clear I want give thanks to you publicly.
yours advisor was profit.
I resolve my problem....!
MArio
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#I2FIrfMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> I posted a reply to your other thread about 10 minutes ago.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
> > The following code represents my problem. It's using Northwind database.
You
> > can copy and paste in the SQL analyzer directly and run so you can see
the
> > problem.
> >
> >
> >
> > My question is: How Can I build a SQL statement but doing replacement of
> > certain values on fly.
> >
> >
> >
> > The causes are error data mismatch.
> >
> >
> >
> > NOTE: I try sp_executeslq and this entire thing.
> >
> >
> >
> >
> >
> > ALTER PROCEDURE dbo.StoredProcedure1
> >
> > /*
> >
> > (
> >
> > @.parameter1 datatype = default value,
> >
> > @.parameter2 datatype OUTPUT
> >
> > )
> >
> > */
> >
> > AS
> >
> >
> >
> > set language us_English
> >
> > set nocount on
> >
> >
> >
> > declare @.BirthDatePos int
> >
> > declare @.colum sysname -- sysname is equal to nvarchar(128)
> >
> >
> >
> > /*
> >
> > * ----
> >
> > * When @.DateFrom and @.DateTo are datetime the SQL-Server
> >
> > * not run. I get the following error message:
> >
> > * Syntax error converting datetime from character string.
> >
> > * ----
> >
> > */
> >
> > declare @.DateFrom datetime
> >
> > declare @.DateTo datetime
> >
> >
> >
> > /*
> >
> > * ---
> >
> > * When @.DateFrom and @.DateTo are both characters the SQL-Server
> >
> > * run but not return anything.
> >
> > * ---
> >
> > */
> >
> > --declare @.DateFrom nvarchar(8)
> >
> > --declare @.DateTo nvarchar(8)
> >
> >
> >
> > /* Creating my Dynamic SQL: */
> >
> > set @.DateFrom = '20040505'
> >
> > set @.DateTo = '20040505'
> >
> > set @.BirthDatePos = 6
> >
> > set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
> >
> >
> >
> > SELECT BirthDate
> >
> > FROM Employees
> >
> > WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> >
> > set nocount off
> >
> > RETURN
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> > > I just posted a reply to this. No need to repost.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > >
> > >
> > > "Mario Reiley" <mreiley@.cantv.net> wrote in message
> > news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > > > In several messages that I post early I need help for create Dynamic
SQL
> > or
> > > > that I trying to do.
> > > >
> > > > Here is an example of my store procedure
> > > >
> > > > ALTER PROCEDURE dbo.Test
> > > > /*
> > > > (
> > > > @.parameter1 datatype = default value,
> > > > @.parameter2 datatype OUTPUT
> > > > )
> > > > */
> > > > AS
> > > >
> > > > SET LANGUAGE us_english
> > > > SET NOCOUNT ON
> > > >
> > > > declare @.Colum sysname
> > > > declare @.MyDate nvarchar(10)
> > > >
> > > > set @.MyDate = '2004-05-05'
> > > > set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> > > >
> > > > print
'---'
> > > > print 'The following expression no generate an error but not return
me'
> > > > print 'anything rows. ( Both are String )'
> > > > print
'---'
> > > >
> > > > -- Both are String
> > > > print 'Expression:'
> > > > print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> > > > print 'Result:'
> > > > print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > > >
> > > > print
'================================================================'
> > > >
> > > > print
> > > >
> >
'----'
> > > > print 'Thi following expression generate an error:(to have types
> > mismatch).'
> > > > print
> > > >
> >
'----'
> > > >
> > > > -- To have types mismatch.
> > > > print 'Expression:'
> > > > print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> > > > print 'Result:'
> > > > print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > > >
> > > > print
'================================================================'
> > > >
> > > > SET NOCOUNT OFF
> > > > RETURN
> > > >
> > > >
> > > > -- The out --
> > > >
> > > >
> > > > Running dbo."Test".
> > > >
> > > > ---
> > > > The following expression no generate an error but not return me
> > > > anything rows. ( Both are String )
> > > > ---
> > > > Expression:
> > > > @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > > > Result:
> > > > FechaMie>=2004-05-05
> > > > ================================================================> > > >
> > > >
> > > >
> > >
> ----
> > > > Thi following expression generate an error:(to have types mismatch).
> > >
> ----
> > > > Expression:
> > > > @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > > > Result:
> > > > Syntax error converting datetime from character string.
> > > > No rows affected.
> > > > (0 row(s) returned)
> > > > @.RETURN_VALUE => > > > Finished running dbo."Test".
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Dynamic SQL another example

In several messages that I post early I need help for create Dynamic SQL or
that I trying to do.
Here is an example of my store procedure
ALTER PROCEDURE dbo.Test
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
SET LANGUAGE us_english
SET NOCOUNT ON
declare @.Colum sysname
declare @.MyDate nvarchar(10)
set @.MyDate = '2004-05-05'
set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
print '---'
print 'The following expression no generate an error but not return me'
print 'anything rows. ( Both are String )'
print '---'
-- Both are String
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
print '=======================================
========================='
print
'----'
print 'Thi following expression generate an errorto have types mismatch).'
print
'----'
-- To have types mismatch.
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
print '=======================================
========================='
SET NOCOUNT OFF
RETURN
-- The out --
Running dbo."Test".
---
The following expression no generate an error but not return me
anything rows. ( Both are String )
---
Expression:
@.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
Result:
FechaMie>=2004-05-05
========================================
========================
----
Thi following expression generate an errorto have types mismatch).
----
Expression:
@.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
Result:
Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running dbo."Test".I just posted a reply to this. No need to repost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.g
bl...
> In several messages that I post early I need help for create Dynamic SQL
or
> that I trying to do.
> Here is an example of my store procedure
> ALTER PROCEDURE dbo.Test
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
> SET LANGUAGE us_english
> SET NOCOUNT ON
> declare @.Colum sysname
> declare @.MyDate nvarchar(10)
> set @.MyDate = '2004-05-05'
> set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> print '---'
> print 'The following expression no generate an error but not return me'
> print 'anything rows. ( Both are String )'
> print '---'
> -- Both are String
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> print '=======================================
========================='
> print
> '----'
> print 'Thi following expression generate an errorto have types mismatch)
.'
> print
> '----'
> -- To have types mismatch.
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> print '=======================================
========================='
> SET NOCOUNT OFF
> RETURN
>
> -- The out --
>
> Running dbo."Test".
> ---
> The following expression no generate an error but not return me
> anything rows. ( Both are String )
> ---
> Expression:
> @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> Result:
> FechaMie>=2004-05-05
> ========================================
========================
>
> ----
> Thi following expression generate an errorto have types mismatch).
> ----
> Expression:
> @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> Result:
> Syntax error converting datetime from character string.
> No rows affected.
> (0 row(s) returned)
> @.RETURN_VALUE =
> Finished running dbo."Test".
>
>|||The following code represents my problem. It's using Northwind database. You
can copy and paste in the SQL analyzer directly and run so you can see the
problem.
My question is: How Can I build a SQL statement but doing replacement of
certain values on fly.
The causes are error data mismatch.
NOTE: I try sp_executeslq and this entire thing.
ALTER PROCEDURE dbo.StoredProcedure1
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
set language us_English
set nocount on
declare @.BirthDatePos int
declare @.colum sysname -- sysname is equal to nvarchar(128)
/*
* ----
* When @.DateFrom and @.DateTo are datetime the SQL-Server
* not run. I get the following error message:
* Syntax error converting datetime from character string.
* ----
*/
declare @.DateFrom datetime
declare @.DateTo datetime
/*
* ---
* When @.DateFrom and @.DateTo are both characters the SQL-Server
* run but not return anything.
* ---
*/
--declare @.DateFrom nvarchar(8)
--declare @.DateTo nvarchar(8)
/* Creating my Dynamic SQL: */
set @.DateFrom = '20040505'
set @.DateTo = '20040505'
set @.BirthDatePos = 6
set @.colum = col_name(object_id('Employees'),@.BirthDa
tePos)
SELECT BirthDate
FROM Employees
WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
set nocount off
RETURN
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> I just posted a reply to this. No need to repost.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
or[vbcol=seagreen]
'----'[vbcol=seagreen]
mismatch).'[vbcol=seagreen]
'----'[vbcol=seagreen]
>|||I posted a reply to your other thread about 10 minutes ago.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gb
l...
> The following code represents my problem. It's using Northwind database. Y
ou
> can copy and paste in the SQL analyzer directly and run so you can see the
> problem.
>
> My question is: How Can I build a SQL statement but doing replacement of
> certain values on fly.
>
> The causes are error data mismatch.
>
> NOTE: I try sp_executeslq and this entire thing.
>
>
> ALTER PROCEDURE dbo.StoredProcedure1
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
>
> set language us_English
> set nocount on
>
> declare @.BirthDatePos int
> declare @.colum sysname -- sysname is equal to nvarchar(128)
>
> /*
> * ----
> * When @.DateFrom and @.DateTo are datetime the SQL-Server
> * not run. I get the following error message:
> * Syntax error converting datetime from character string.
> * ----
> */
> declare @.DateFrom datetime
> declare @.DateTo datetime
>
> /*
> * ---
> * When @.DateFrom and @.DateTo are both characters the SQL-Server
> * run but not return anything.
> * ---
> */
> --declare @.DateFrom nvarchar(8)
> --declare @.DateTo nvarchar(8)
>
> /* Creating my Dynamic SQL: */
> set @.DateFrom = '20040505'
> set @.DateTo = '20040505'
> set @.BirthDatePos = 6
> set @.colum = col_name(object_id('Employees'),@.BirthDa
tePos)
>
> SELECT BirthDate
> FROM Employees
> WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> set nocount off
> RETURN
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> or
> '----'
> mismatch).'
> '----'
>|||Mr. Tibor You code was enough clear I want give thanks to you publicly.
yours advisor was profit.
I resolve my problem....!
MArio
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#I2FIrfMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> I posted a reply to your other thread about 10 minutes ago.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
You[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
SQL[vbcol=seagreen]
'---'[vbcol=seagreen]
me'[vbcol=seagreen]
'---'[vbcol=seagreen]
'=======================================
========================='[vbcol=seagree
n]
'----'[vbcol=seagreen]
'----'[vbcol=seagreen]
'=======================================
========================='[vbcol=seagree
n]
> ----
> ----
>