Dynamic SQL Command: below

Showing posts with label below. Show all posts

Tuesday, March 27, 2012

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)
did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear examnotes <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>sql

Wednesday, March 21, 2012

Dynamicly create report

Hi All,
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill

Dynamically supress drill-down enabled groups ?

Hi,
We are trying to find if it is possible to customize grouping with
drill-down in a way as explained below. Any help is appreciated. Thanks.
The structure of the data is such that a group can contain subgroups which
in turn contain detail rows. However, in some cases
a group does not have subgroups (actually has one subgroup which represents
group itself). In our table-based reports this is defined as :
Col 1 Col2 Col3 ...
---
Group | | | (group by
Fields!group)
---
Subgroup | | | (group by
Fields!subgroup)
---
Detail | | |
---
Oupt of this report is as follows:
Figure 1 :
--
+ Group 1
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
+ Group 2
+ SubGroup 1
Detail 1
+ Subgroup 2
Detail 1
Detail 2
...
Both group and subgroup feature toggle item that hides or shows their
children rows. However, as explained above sometimes a group may not have
subgroups. In that case query returns one subgroup which has the same name as
the main group.
In example below Group1 only has one subgroup. That subgroup represents
actually the main group itself. Thus it is redundant.
Figure 2:
--
+ Group 1
+ Group 1
Detail 1
Detail 2
Detail 3
+ Group 2
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
In the case as shown above we would like to have something like:
Figure 3
--
+ Group 1
Detail 1
Detail 2
Detail 3
+ Group 2
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
Note that subgroup of Group1 has been supressed in the example above. The
details that were in subgroup of Group 1 were moved up the hierarchy and now
are shown as part of the main group. We can find out if the subgroups need
to be supressed by using CountDistinct for example. However we do not know of
way to supress the subgroups like in example above once we have the
information that it should be supressed. Again, please note that we would
like to use drilldown feature and toggle-item functionality with this.The current feature set of the matrix region doesn't include the
functionality you are asking for. Instead, I will try to massage the data at
the data store to give me the hierarchy I need.
--
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/
---
"Vedad" <Vedad@.discussions.microsoft.com> wrote in message
news:8E2B91AE-854E-40C0-9964-41127BBF08A4@.microsoft.com...
> Hi,
> We are trying to find if it is possible to customize grouping with
> drill-down in a way as explained below. Any help is appreciated. Thanks.
> The structure of the data is such that a group can contain subgroups which
> in turn contain detail rows. However, in some cases
> a group does not have subgroups (actually has one subgroup which
represents
> group itself). In our table-based reports this is defined as :
> Col 1 Col2 Col3 ...
> ---
> Group | | | (group by
> Fields!group)
> ---
> Subgroup | | | (group by
> Fields!subgroup)
> ---
> Detail | | |
> ---
>
> Oupt of this report is as follows:
>
> Figure 1 :
> --
> + Group 1
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> + Group 2
> + SubGroup 1
> Detail 1
>
> + Subgroup 2
> Detail 1
> Detail 2
> ...
> Both group and subgroup feature toggle item that hides or shows their
> children rows. However, as explained above sometimes a group may not have
> subgroups. In that case query returns one subgroup which has the same name
as
> the main group.
> In example below Group1 only has one subgroup. That subgroup represents
> actually the main group itself. Thus it is redundant.
>
> Figure 2:
> --
> + Group 1
> + Group 1
> Detail 1
> Detail 2
> Detail 3
> + Group 2
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> In the case as shown above we would like to have something like:
> Figure 3
> --
> + Group 1
> Detail 1
> Detail 2
> Detail 3
> + Group 2
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> Note that subgroup of Group1 has been supressed in the example above. The
> details that were in subgroup of Group 1 were moved up the hierarchy and
now
> are shown as part of the main group. We can find out if the subgroups
need
> to be supressed by using CountDistinct for example. However we do not know
of
> way to supress the subgroups like in example above once we have the
> information that it should be supressed. Again, please note that we would
> like to use drilldown feature and toggle-item functionality with this.sql

Monday, March 19, 2012

Dynamically Populated Columns

Hi everyone,
I have to create report that has dynamically populated columns. The data
example is below:
Zone Mar-06 Apr-06 May-06
---
Med 1841.096666 1955.371600 2359.752608
Sub 0.000000 0.000000 0.000000
Work 1353.824214 945.390865 1542.416949
West 1022.000000 0.000000 0.000000
Nat'l 0.000000 0.000000 0.000000
And month columns are added dynamically. Like so:
Zone Mar-06 Apr-06 May-06
Jun-06 Jul-06 Aug-06
------
Med 1841.096666 1955.371600 2359.752608 1098.842727
1827.094062 1381.066666
Sub 0.000000 0.000000 0.000000
0.000000 0.000000 0.000000
Work 1353.824214 945.390865 1542.416949 1176.967816
1192.468396 621.960000
West 1022.000000 0.000000 0.000000
0.000000 1353.905000 0.000000
Nat'l 0.000000 0.000000 0.000000
0.000000 0.000000 0.000000
How can I design a report so that it grows with however many columns are
returned from the SP.
Thanks everybody for your input.Check out matrix control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CI" <ci@.ci.com> wrote in message
news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> I have to create report that has dynamically populated columns. The data
> example is below:
> Zone Mar-06 Apr-06 May-06
> ---
> Med 1841.096666 1955.371600 2359.752608
> Sub 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949
> West 1022.000000 0.000000 0.000000
> Nat'l 0.000000 0.000000 0.000000
> And month columns are added dynamically. Like so:
> Zone Mar-06 Apr-06 May-06
> Jun-06 Jul-06 Aug-06
> ------
> Med 1841.096666 1955.371600 2359.752608 1098.842727
> 1827.094062 1381.066666
> Sub 0.000000 0.000000 0.000000
> 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949 1176.967816
> 1192.468396 621.960000
> West 1022.000000 0.000000 0.000000 0.000000
> 1353.905000 0.000000
> Nat'l 0.000000 0.000000 0.000000 0.000000
> 0.000000 0.000000
> How can I design a report so that it grows with however many columns are
> returned from the SP.
> Thanks everybody for your input.
>|||I did,
However I am not sure how to map the top columns to it. In the dataset that
I get back I do not have the reference to the Mar-06 Apr-06
May-06 columns.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23ySQzoc1GHA.1304@.TK2MSFTNGP05.phx.gbl...
> Check out matrix control.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "CI" <ci@.ci.com> wrote in message
> news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> I have to create report that has dynamically populated columns. The data
>> example is below:
>> Zone Mar-06 Apr-06 May-06
>> ---
>> Med 1841.096666 1955.371600 2359.752608
>> Sub 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> West 1022.000000 0.000000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> And month columns are added dynamically. Like so:
>> Zone Mar-06 Apr-06 May-06
>> Jun-06 Jul-06 Aug-06
>> ------
>> Med 1841.096666 1955.371600 2359.752608 1098.842727
>> 1827.094062 1381.066666
>> Sub 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> 1176.967816 1192.468396 621.960000
>> West 1022.000000 0.000000 0.000000 0.000000
>> 1353.905000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> How can I design a report so that it grows with however many columns are
>> returned from the SP.
>> Thanks everybody for your input.
>>
>|||i am new to this RS2005. i have same fucntionality with interactive
sorting... any steps to implement this functionality...,, please send
me link or code
Thanks
vinod
CI wrote:
> I did,
> However I am not sure how to map the top columns to it. In the dataset that
> I get back I do not have the reference to the Mar-06 Apr-06
> May-06 columns.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23ySQzoc1GHA.1304@.TK2MSFTNGP05.phx.gbl...
> > Check out matrix control.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "CI" <ci@.ci.com> wrote in message
> > news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> >> Hi everyone,
> >> I have to create report that has dynamically populated columns. The data
> >> example is below:
> >>
> >> Zone Mar-06 Apr-06 May-06
> >> ---
> >> Med 1841.096666 1955.371600 2359.752608
> >> Sub 0.000000 0.000000 0.000000
> >> Work 1353.824214 945.390865 1542.416949
> >> West 1022.000000 0.000000 0.000000
> >> Nat'l 0.000000 0.000000 0.000000
> >>
> >> And month columns are added dynamically. Like so:
> >>
> >> Zone Mar-06 Apr-06 May-06
> >> Jun-06 Jul-06 Aug-06
> >> ------
> >> Med 1841.096666 1955.371600 2359.752608 1098.842727
> >> 1827.094062 1381.066666
> >> Sub 0.000000 0.000000 0.000000
> >> 0.000000 0.000000 0.000000
> >> Work 1353.824214 945.390865 1542.416949
> >> 1176.967816 1192.468396 621.960000
> >> West 1022.000000 0.000000 0.000000 0.000000
> >> 1353.905000 0.000000
> >> Nat'l 0.000000 0.000000 0.000000
> >> 0.000000 0.000000 0.000000
> >>
> >> How can I design a report so that it grows with however many columns are
> >> returned from the SP.
> >>
> >> Thanks everybody for your input.
> >>
> >>
> >
> >|||Oh, so you are getting back a varying number of columns? I thought you
wanted to take your data and get variable columns. RS cannot deal with
multiple columns. However, you could probably write your SP to not create
the variable columns and let RS do it with the matrix control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CI" <ci@.ci.com> wrote in message
news:e%23fdm$c1GHA.2036@.TK2MSFTNGP05.phx.gbl...
>I did,
> However I am not sure how to map the top columns to it. In the dataset
> that I get back I do not have the reference to the Mar-06
> Apr-06 May-06 columns.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23ySQzoc1GHA.1304@.TK2MSFTNGP05.phx.gbl...
>> Check out matrix control.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "CI" <ci@.ci.com> wrote in message
>> news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> I have to create report that has dynamically populated columns. The data
>> example is below:
>> Zone Mar-06 Apr-06 May-06
>> ---
>> Med 1841.096666 1955.371600 2359.752608
>> Sub 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> West 1022.000000 0.000000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> And month columns are added dynamically. Like so:
>> Zone Mar-06 Apr-06 May-06
>> Jun-06 Jul-06 Aug-06
>> ------
>> Med 1841.096666 1955.371600 2359.752608
>> 1098.842727 1827.094062 1381.066666
>> Sub 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> 1176.967816 1192.468396 621.960000
>> West 1022.000000 0.000000 0.000000 0.000000
>> 1353.905000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> How can I design a report so that it grows with however many columns are
>> returned from the SP.
>> Thanks everybody for your input.
>>
>>
>|||Everybody,
Thanks for your help. What i ended up doing is I unpivot the result set and
now i can use the Matrix to do it all for me
"CI" <ci@.ci.com> wrote in message
news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> I have to create report that has dynamically populated columns. The data
> example is below:
> Zone Mar-06 Apr-06 May-06
> ---
> Med 1841.096666 1955.371600 2359.752608
> Sub 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949
> West 1022.000000 0.000000 0.000000
> Nat'l 0.000000 0.000000 0.000000
> And month columns are added dynamically. Like so:
> Zone Mar-06 Apr-06 May-06
> Jun-06 Jul-06 Aug-06
> ------
> Med 1841.096666 1955.371600 2359.752608 1098.842727
> 1827.094062 1381.066666
> Sub 0.000000 0.000000 0.000000
> 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949 1176.967816
> 1192.468396 621.960000
> West 1022.000000 0.000000 0.000000 0.000000
> 1353.905000 0.000000
> Nat'l 0.000000 0.000000 0.000000 0.000000
> 0.000000 0.000000
> How can I design a report so that it grows with however many columns are
> returned from the SP.
> Thanks everybody for your input.
>

Sunday, March 11, 2012

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.

I suppose I could just do utbl1, utbl2, utb1234 couldn't I...

> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.

> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.

> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.

> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.

> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:
Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegro ups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.
|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/is.../default.aspx.
|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...

> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)

> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.

> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.

> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.

> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
--
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Sunday, February 26, 2012

Dynamic update of a column

Hi,
In the code below, I am trying to update a column to '0'. But, I dont know
which column to update until my variable @. Month fetches the column name.
DECLARE @.Month Varchar(20)
SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
WHERE MD.lMonthId = DTS.JobMonth
and DTS.JobId = 2
PRINT @.Month
update FTEActualForecast set @.Month = 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
Now, the problem is
1. The column I am trying to update to '0'is determined dynamically, so I
have used a variable name called @.Month
2. But in the UPDATE statement when I use the keyword SET it sets the
variable and not the column value to ‘zero’
Is there some workaround or better way of doing this ?
Please advise.
Cheers,
Hemil.Try
Exec ('update FTEActualForecast set ' + @.Month + '= 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
"Hemil" wrote:

> Hi,
> In the code below, I am trying to update a column to '0'. But, I dont kno
w
> which column to update until my variable @. Month fetches the column name.
> DECLARE @.Month Varchar(20)
> SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
> WHERE MD.lMonthId = DTS.JobMonth
> and DTS.JobId = 2
> PRINT @.Month
> update FTEActualForecast set @.Month = 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> Now, the problem is
> 1. The column I am trying to update to '0'is determined dynamically, so I
> have used a variable name called @.Month
> 2. But in the UPDATE statement when I use the keyword SET it sets the
> variable and not the column value to ‘zero’
>
> Is there some workaround or better way of doing this ?
> Please advise.
> Cheers,
> Hemil.
>|||Thanks a lot, Tom.
It does the job.
Thanks again,
Hemil.
"Tom" wrote:
> Try
> Exec ('update FTEActualForecast set ' + @.Month + '= 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
>
> "Hemil" wrote:
>|||Please include DDL with questions like this so that we don't have to guess
what your table looks like.
Why separate columns for each month? Instead, add a DATETIME column to
record the month then you can specify which row to update with a WHERE
clause.
David Portas
SQL Server MVP
--

Friday, February 24, 2012

Dynamic sub-reports with parameter passing?

Hello -

I wanted to know if we can have a fully dynamic sub-reports in CR. I will explain my scenario below..please let me know how i should proceed. I have two sub-reports which uses two different stored procedures to get the required data. Both the stored procedures will take three parameters each.

Sub-Report1: AIM.rpt which uses stored procedure "aim_sp"
Sub-Report1: IND.rpt which uses stored procedure "ind_sp"

aim_sp will take "start_dt","end_dt" and "unit" as parameters
ind_sp will take "start_dt","end_dt" and "unit" as parameters as well.

My requirement is to get the required parameters from the main report(main.rpt). These two sub-reports are shown using this blank report file. The only functionality of the main.rpt is to embed the two sub-reports and get the three parameters. And then, pass the parameters from main report to the two sub-reports. And then, the sub-reports will use the two sub-reports to run the stored procedures and get the data dynamically.

What i did??
----

I created the blank main.rpt file and created three parameters. And then i attached the sub reports to the main report. The sub-reports are themselves seperate report files which have stored procedure as database fields. But when i refresh the main report, it is totally looking for nine parameters. Three are from the main report file. Next three are from the stored procedure "aim_sp" and the next are from "ind_sp".

How can we link the main report parameters to all the sub-report parameters in the back ground and let it ask only three parameters?

Please advice me...
THanks,Right-click on subreport whilst its embedded on main report, select Change Subreport Links option. I will use date fields as an example.
Select the ? fields (paramater fields) from the "Available Fields" list, and when you add them to "Fields To Link To", you should be able to link them to a data field in your subreport. That's provided you have a date field in your subreport...you'll need one, otherwise you can't do the selection. If you don't want to show the date field in the subreport, suppress it, it will still be evaluated against.
Then in subreport's Report menu, select Edit selection Formula and select Record... option.
formula would look something like this:
{Database.DateField} >={?dteStart} AND {Database.DateField} <={?dteEnd}

That should work, from what I can recall.

dave

Sunday, February 19, 2012

Dynamic SQL!

As much as i would prefer not to..it appears the only way i can acheive
something is to use dynamic sql.
I have constructed the test query below, but only recieve invalid column
name errors.
Can someone tell me where i am going wrong?
I am obvsiouly missing something quite simple'
DECLARE @.sqlquery VARCHAR(400)
SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
PRINT @.sqlqueryAdam,
Why do you believe Dynamic SQL is the only way?
Try :
DECLARE @.sqlquery VARCHAR(400)
SET @.sqlquery = 'SELECT asmt_v2_question_id FROM asmt_v2_questions'
EXEC (@.sqlquery)
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Use single quotes to enclose your SQL statement literal like the example
below. Double-quotes should be used only to enclose identifiers when SET
QUOTED_IDENTIFIER is on. The 'invalid column' error results because SQL
Server thinks you are specifying a column name.
SET @.sqlquery = 'SELECT asmt_v2_question_id FROM asmt_v2_questions'
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Also,
If you haven't already read it, you might want to take a look at Erland's
article:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Hi Jerry,
The query below is the one in question.
I am still learning the functions and logic structures of TSQL .
Consequently, so far i can't think of anyway of doing the following without
Dynamic Sql (which i would prefer not to).
A previous post also dealt with this issue (been trying to nut this query
out for a while).
I received some responses, though quite helpful and very much appreciated, i
am not sure they really dealt with my particular problem.
(This would be my fault for providing an overly simplified query).
The issue is parameterizing the "TOP" statement .
Or somehow limiting dynamically (via parameter, variable) the number of
rows returned by the SECOND (Only) query in the UNION.
-- select question details where questions are compulsory, within a given
area
-- and not answered in a previous completed assessment attempt
SELECT
NewID(),asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
WHERE qtype = 'c' AND asmt_v2_area_id = @.aid AND asmt_v2_question_id
NOT IN (
-- retreive question ids (compulsory) in 'asmt_question_results' table
-- that are associated with a given set of attempt_ids
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'c'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
-- retreive attempt_ids that are associated with completed assessments
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
UNION ALL
-- select question details from results returned in sub query
SELECT
NEWID() AS ID, asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM (
-- select question details where questions are optional, within a given area
-- and not answered in a previous completed assessment attempt
-- only return a specified number of rows
SELECT
TOP 2 NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype,
answer_url /* TOP STATEMENT TO BE PARAMETERISE */
FROM
asmt_v2_questions
WHERE
qtype = 'o'
AND
asmt_v2_area_id = @.aid
AND
asmt_v2_question_id
NOT IN (
-- retreive question ids (optional questions) in 'asmt_question_results'
table
-- that are associated with a given set of attempt_ids
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'o'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
-- retreive attempt_ids that are associated with completed assessments
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid
AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
ORDER BY 1|||Try SET ROWCOUNT in BOL.
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eEmek27xFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Hi Jerry,
> The query below is the one in question.
> I am still learning the functions and logic structures of TSQL .
> Consequently, so far i can't think of anyway of doing the following
> without Dynamic Sql (which i would prefer not to).
> A previous post also dealt with this issue (been trying to nut this query
> out for a while).
> I received some responses, though quite helpful and very much appreciated,
> i am not sure they really dealt with my particular problem.
> (This would be my fault for providing an overly simplified query).
> The issue is parameterizing the "TOP" statement .
> Or somehow limiting dynamically (via parameter, variable) the number of
> rows returned by the SECOND (Only) query in the UNION.
>
> -- select question details where questions are compulsory, within a given
> area
> -- and not answered in a previous completed assessment attempt
> SELECT
> NewID(),asmt_v2_question_id, qtext, qindex, qtype, answer_url
> FROM
> asmt_v2_questions
> WHERE qtype = 'c' AND asmt_v2_area_id = @.aid AND asmt_v2_question_id
> NOT IN (
> -- retreive question ids (compulsory) in 'asmt_question_results' table
> -- that are associated with a given set of attempt_ids
> SELECT DISTINCT
> asmt_v2_questions.asmt_v2_question_id
> FROM
> asmt_v2_questions
> INNER JOIN
> asmt_v2_question_results
> ON
> asmt_v2_questions.asmt_v2_question_id =
> asmt_v2_question_results.asmt_v2_question_id
> WHERE
> asmt_v2_questions.qtype = 'c'
> AND
> asmt_v2_question_results.asmt_v2_attempt_id
> IN (
> -- retreive attempt_ids that are associated with completed assessments
> SELECT
> asmt_v2_completed.asmt_v2_attempt_id
> FROM
> asmt_v2_completed
> INNER JOIN
> asmt_v2_attempts
> ON
> asmt_v2_completed.asmt_v2_attempt_id =
> asmt_v2_attempts.asmt_v2_attempt_id
> WHERE
> asmt_v2_attempts.asmt_v2_employee_id = @.eid AND
> asmt_v2_attempts.asmt_v2_area_id = @.aid
> )
> )
> UNION ALL
> -- select question details from results returned in sub query
> SELECT
> NEWID() AS ID, asmt_v2_question_id, qtext, qindex, qtype, answer_url
> FROM (
> -- select question details where questions are optional, within a given
> area
> -- and not answered in a previous completed assessment attempt
> -- only return a specified number of rows
> SELECT
> TOP 2 NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype,
> answer_url /* TOP STATEMENT TO BE PARAMETERISE */
> FROM
> asmt_v2_questions
> WHERE
> qtype = 'o'
> AND
> asmt_v2_area_id = @.aid
> AND
> asmt_v2_question_id
> NOT IN (
> -- retreive question ids (optional questions) in
> 'asmt_question_results' table
> -- that are associated with a given set of attempt_ids
> SELECT DISTINCT
> asmt_v2_questions.asmt_v2_question_id
> FROM
> asmt_v2_questions
> INNER JOIN
> asmt_v2_question_results
> ON
> asmt_v2_questions.asmt_v2_question_id =
> asmt_v2_question_results.asmt_v2_question_id
> WHERE
> asmt_v2_questions.qtype = 'o'
> AND
> asmt_v2_question_results.asmt_v2_attempt_id
> IN (
> -- retreive attempt_ids that are associated with completed assessments
> SELECT
> asmt_v2_completed.asmt_v2_attempt_id
> FROM
> asmt_v2_completed
> INNER JOIN
> asmt_v2_attempts
> ON
> asmt_v2_completed.asmt_v2_attempt_id =
> asmt_v2_attempts.asmt_v2_attempt_id
> WHERE
> asmt_v2_attempts.asmt_v2_employee_id = @.eid
> AND
> asmt_v2_attempts.asmt_v2_area_id = @.aid
> )
> )
> ORDER BY 1
>|||Umm..as mentioned in my post i only want to limit the number of rows
returned in the SECOND query of the UNION.
Unless i am MISSING something, SET ROWCOUNT witll apply to all results
returned in the query!
Adam
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Vg10f.2316$Hd2.2048@.fe11.lga...
> Try SET ROWCOUNT in BOL.
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:eEmek27xFHA.2556@.TK2MSFTNGP10.phx.gbl...
>|||Adam Knight wrote:
> Umm..as mentioned in my post i only want to limit the number of rows
> returned in the SECOND query of the UNION.
> Unless i am MISSING something, SET ROWCOUNT witll apply to all results
> returned in the query!
>
You can store the smaller result set (probably the one with the TOP in a
temp table) and then union the temp table with the other query and avoid
dynamic sql altogether.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||THANK YOU!!!!!
I think we may have a useable option.
One last question to nail this.
I am trying to create the temp table and insert the relevant data.
I am recieving this error:
Insert Error: Column name or number of supplied values does not match table
definition.
Any thoughts?
As far as i can tell the column definitions are the same..
CREATE TABLE #tempTable (
[asmt_v2_question_id] [int] NOT NULL,
[qtext] [varchar](255) NULL,
[qindex] [tinyint] NULL,
[qtype] [char](1) NULL,
[answer_url] [varchar](255) NULL
)
INSERT INTO #tempTable
SELECT
asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
WHERE
qtype = 'o'
AND
asmt_v2_area_id = @.aid
AND
asmt_v2_question_id
NOT IN (
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'o'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid
AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
ORDER BY NEWID()
SELECT * FROM #tempTable
CREATE TABLE [dbo].[asmt_v2_questions](
[asmt_v2_question_id] [int] IDENTITY(1,1) NOT NULL,
[asmt_v2_area_id] [int] NULL,
[qtext] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[qindex] [tinyint] NULL,
[qtype] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[answer_url] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_assmnt_questions] PRIMARY KEY CLUSTERED
(
[asmt_v2_question_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]|||Inside the INSERT...SELECT statement you must explicitly state which columns
you're inserting into.
INSERT INTO #tempTable
(
asmt_v2_question_id
,qtext
,qindex
,qtype
,answer_url
)
SELECT
asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
<snip>
ML

Friday, February 17, 2012

Dynamic SQL Query

I am having trouble getting this dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.
I want to build this query with only values that have values that
are passed, if they are null, no need to be included in query. This
almost seems to work
except I have issues with getting the query to work when the field is
an int.
I get this error
Syntax error converting the varchar value 'select * from company where
categoryid = '' to a column of data type int.
Assistance would be appreciated.
_________________________________
CREATE PROCEDURE stp_Search
@.categoryid int = NULL,
@.commodityid int = NULL,
@.companyname varchar(25) = NULL,
@.streetname varchar(25) = NULL,
@.communityid int = NULL,
@.status varchar(1) = NULL
AS
DECLARE @.sql varchar(4000)
SELECT @.sql = 'select * from company '
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where categoryid = ''' + @.categoryid + ''''
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
IF @.companyname IS NOT NULL
SELECT @.sql = @.sql + ' and companyname LIKE ''' + @.companyname +
''''
IF @.status IS NOT NULL
SELECT @.sql = @.sql + ' AND status LIKE ''' + @.status + ''''
EXEc(@.sql)pisquem@.hotmail.com,
Are you sure you want to concatenate the logical expressions below, based on
@.categoryid?

> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
They way you are doing it is not a good practice. Try using parameters with
sp_executesql and you will not have to worry about casting, sql injection,
etc.
SELECT @.sql = 'select c1, c2, ..., cn from dbo.company where 1 = 1'
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' and categoryid = @.categoryid'
IF @.commodityid IS NOT NULL
SELECT @.sql = @.sql + ' and commodityid = @.commodityid'
IF communityid IS NOT NULL
SELECT @.sql = @.sql + ' where communityid = @.communityid'
IF @.companyname IS NOT NULL
SELECT @.sql = @.sql + ' and companyname = @.companyname'
IF @.status IS NOT NULL
SELECT @.sql = @.sql + ' and status = @.status'
exec sp_executesql @.sql, N'@.categoryid int, @.commodityid int, @.companyname
varchar(25), @.streetname varchar(25), @.communityid int, @.status varchar(1)',
@.categoryid, @.commodityid, @.communityid, @.companyname, @.status
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"pisquem@.hotmail.com" wrote:

> I am having trouble getting this dynamic query in a sp working and the
> code is below so can someone please help me? It would be very much
> appreciated.
> I want to build this query with only values that have values that
> are passed, if they are null, no need to be included in query. This
> almost seems to work
> except I have issues with getting the query to work when the field is
> an int.
> I get this error
> Syntax error converting the varchar value 'select * from company where
> categoryid = '' to a column of data type int.
>
> Assistance would be appreciated.
> _________________________________
> CREATE PROCEDURE stp_Search
> @.categoryid int = NULL,
> @.commodityid int = NULL,
> @.companyname varchar(25) = NULL,
> @.streetname varchar(25) = NULL,
> @.communityid int = NULL,
> @.status varchar(1) = NULL
> AS
> DECLARE @.sql varchar(4000)
> SELECT @.sql = 'select * from company '
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where categoryid = ''' + @.categoryid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
> IF @.companyname IS NOT NULL
> SELECT @.sql = @.sql + ' and companyname LIKE ''' + @.companyname +
> ''''
> IF @.status IS NOT NULL
> SELECT @.sql = @.sql + ' AND status LIKE ''' + @.status + ''''
> EXEc(@.sql)
>

Dynamic SQL Problem

I'm getting "Invalid Column" error with below code. Can anyone Help?
CODE:
USE [Northwind]
GO
declare @.SQL varchar(1000), @.debug int
declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
int
set @.sTable="Orders"
set @.sField="OrderDate"
set @.sField2="employeeID"
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
--EXEC(@.SQL)Your @.variables are not passed into the Exec(), nor should they be. The
Exec() is run separately from the stored proceedure.
Here is a simple example of how I circumvent this:
declare @.SQL varchar(1000)
declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
set @.getTable='Orders'
set @.getField='OrderDate'
set @.getFilter='employeeID = 3'
SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
Set @.SQL = Replace(@.SQL,'getField',@.getField)
Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
EXEC(@.SQL)
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||that's fine except i need a @.employeeID variable. i hardcoded 3 just for
this simple example and will actually be passing 2 WHERE variables in the
production code. can you modify your code?
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:145010B5-D41D-4184-BE78-7F239164BD44@.microsoft.com...
> Your @.variables are not passed into the Exec(), nor should they be. The
> Exec() is run separately from the stored proceedure.
> Here is a simple example of how I circumvent this:
> declare @.SQL varchar(1000)
> declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
> set @.getTable='Orders'
> set @.getField='OrderDate'
> set @.getFilter='employeeID = 3'
> SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
> Set @.SQL = Replace(@.SQL,'getField',@.getField)
> Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
> Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
> EXEC(@.SQL)
>
> "Scott" wrote:
>|||My goof. Your doing pretty much what I am. However, you have double quotes
around you @.variables.
Also watch out for empty space, by declaring as char rather than varchar,
your string is longer than it needs to be.
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||You main problem is that you delimited your strings with double quote marks.
In T-SQL, strings are delimited with single quotes. i.e. set @.sTable='Order
s'
For readability of the print @.sql, I changed your variables from char(40) to
varchar(40). The code will execute fine with char(40), it just has a lot of
extra spaces.
If you change the variables to varchar or you happen to have a table name of
40 characters, the WHERE clause will fail because there will be no space
between them. I suggest you put a space in front of the word WHERE as I've
done below.
Finally, EXEC (@.SQL) is no longer the recommended best practices. You
should be using exex sp_executesql which requires unicode input so I changed
@.SQL from varchar(1000) to nvarchar(1000).
Hope that helps,
Joe
Here's the corrected code:
USE [Northwind]
GO
declare @.SQL nvarchar(1000), @.debug int
declare @.sTable varchar(40), @.sField varchar(40), @.sField2 varchar(40),
@.employeeID int
set @.sTable='Orders'
set @.sField='OrderDate'
set @.sField2='employeeID'
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + ' WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
exec sp_executesql @.sql
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||Parameterised dynamic queries are best done using the sp_executesql system
procedure. No hassle, no fuss - pure execution.
ML
http://milambda.blogspot.com/

Tuesday, March 27, 2012

Easy Way Determine Hourly Integer Value from Field DateTime Format

Easy Way Determine Hourly Integer Value from Field DateTime Format

Easy Way Determine Hourly Integer Value from Field DateTime Format

Wednesday, March 21, 2012

Dynamicly create report

Dynamically supress drill-down enabled groups ?

Monday, March 19, 2012

Dynamically Populated Columns

Sunday, March 11, 2012

Dynamically generate table name?

Dynamically generate table name?

Dynamically generate table name?

Dynamically generate table name?

Sunday, February 26, 2012

Dynamic update of a column

Friday, February 24, 2012

Dynamic sub-reports with parameter passing?

Sunday, February 19, 2012

Dynamic SQL!

Friday, February 17, 2012

Dynamic SQL Query

Dynamic SQL Problem

Dynamic SQL Command

Blog Archive

About Me