Hi, All,
I am using SQL Server 2000.
I want to dynamically build a SQL statement for pivot query, which come out
more than the character limits for variable and sp_executesql..
What I need is: a query to return all security_level for all User_Id for all
screen_id. We have more than 280 user accounts in AcctUsers table. The
Output format need to be:
Screen User1 User2 User3 .....
Window1 0 1 2
Window2 3 2 0
I used cursor to get user_ids from AcctUsers table, then dynamically build
the SQL statment based on the User_ID. It works fine for up to about 40
user_ids. But it out off the chararter limit for variable nverachar and
sp_executesql. The way that I choose dynamical SQL because the business user
may add new user_id to AcctUsers table and then run a report, which will
call this query.
Can anyone help me?
Thanks.
Perayu
Here are the DDL:
CREATE TABLE [dbo].[AcctUsers] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Security] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[screen_id] [smallint] NOT NULL ,
[security_level] [smallint] NOT NULL
) ON [PRIMARY]
GOHi!
Do please check this: http://www.sommarskog.se/dynamic_sql.html#use-which.
I would suggest you to read the complete article.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi, All,
> I am using SQL Server 2000.
> I want to dynamically build a SQL statement for pivot query, which come
> out more than the character limits for variable and sp_executesql..
> What I need is: a query to return all security_level for all User_Id for
> all screen_id. We have more than 280 user accounts in AcctUsers table. The
> Output format need to be:
> Screen User1 User2 User3 .....
> Window1 0 1 2
> Window2 3 2 0
> I used cursor to get user_ids from AcctUsers table, then dynamically build
> the SQL statment based on the User_ID. It works fine for up to about 40
> user_ids. But it out off the chararter limit for variable nverachar and
> sp_executesql. The way that I choose dynamical SQL because the business
> user may add new user_id to AcctUsers table and then run a report, which
> will call this query.
> Can anyone help me?
> Thanks.
> Perayu
> Here are the DDL:
> CREATE TABLE [dbo].[AcctUsers] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Security] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [screen_id] [smallint] NOT NULL ,
> [security_level] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>|||Great article!
Here is the trick that I will try, nesting EXEC:
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
Thanks for your help!
Perayu
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:Oqh49AUPGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi!
> Do please check this: http://www.sommarskog.se/dynamic_sql.html#use-which.
> I would suggest you to read the complete article.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||If unicode is not a requirement, you can store 8000 chars in varchar. The
unicode nvarchar will only store 4000.
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi, All,
> I am using SQL Server 2000.
> I want to dynamically build a SQL statement for pivot query, which come
> out more than the character limits for variable and sp_executesql..
> What I need is: a query to return all security_level for all User_Id for
> all screen_id. We have more than 280 user accounts in AcctUsers table. The
> Output format need to be:
> Screen User1 User2 User3 .....
> Window1 0 1 2
> Window2 3 2 0
> I used cursor to get user_ids from AcctUsers table, then dynamically build
> the SQL statment based on the User_ID. It works fine for up to about 40
> user_ids. But it out off the chararter limit for variable nverachar and
> sp_executesql. The way that I choose dynamical SQL because the business
> user may add new user_id to AcctUsers table and then run a report, which
> will call this query.
> Can anyone help me?
> Thanks.
> Perayu
> Here are the DDL:
> CREATE TABLE [dbo].[AcctUsers] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Security] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [screen_id] [smallint] NOT NULL ,
> [security_level] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>
