Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kritisql

Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti

Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti

Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

dynamic reporting

Hi Experts...
I want to create report on the fly, for given mdx query , I mean without
having a pre build report format, and I am using FoodMart 2000 sample
database.
pls give me some ideas.....
Thanks in Advance...If I understand what you mean correctly (and using RS) . . . . . I guess
you'd need to create the RDL file dynamically and have it executed
(I think) 8^)
- peteZ
"Sumudu Prasad" <sumudu@.logicalasia.com> wrote in message
news:uo%23XGEhmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Hi Experts...
> I want to create report on the fly, for given mdx query , I mean without
> having a pre build report format, and I am using FoodMart 2000 sample
> database.
> pls give me some ideas.....
> Thanks in Advance...
>
>

Dynamic Reference to Linked Server

Dear all
I got a problem that I would like to use a dynamic statement to query data
from a linked server (it is not guaranteed that the used database remains on
this server or the Test-Database would be renamed).
I tried to use the OPENQUERY-Functionality or to set up a SP which uses
Parameters
Example (SP with OPENQUERY)I:
ALTER PROCEDURE sp_GetParameterTable
@.LnkSrv NVARCHAR(30), @.LnkTbl NVARCHAR(30)
AS
SELECT
*
FROM
OPENQUERY
(
LTRIM(@.LnkSrv), -- LinkedServer
'
SELECT
*
FROM ' +
LTRIM(@.LnkTbl)
+ '
WHERE
(X_TYPE = ''XYZ'')
ORDER BY
CONVERT(FLOAT,X_KEY) DESC
'
)
->> it won't work !!
the other way to use a a SELECT Statement and a Function to get the Table
won't also not work.
SELECT
TOP 1
CONVERT(FLOAT,X_KEY) AS LastUsedKey
FROM
lokalserver.dbo.fn_Test
(
LinkedServer,
DataBase,
Table
)
WHERE
(X_TYPE = 'XYZ')
ORDER BY
CONVERT(FLOAT,X_KEY) DESC
-->> Function
ALTER FUNCTION fn_Test
(
@.SrvName NVARCHAR(20),
@.DBName NVARCHAR(20),
@.tblName NVARCHAR(35)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @.DBRef NVARCHAR(80)
SET @.DBRef = LTRIM(@.SrvName) + '.'
+ LTRIM(@.DBName ) + '.dbo.'
+ LTRIM(@.tblName )
RETURN(LTRIM(@.DBRef))
END
This will also not working.
Could anybody help me in this context? Any Help will be appreciated.
Youry,
BrunoSomething like this should work - I have used it:
DECLARE @.w nvarchar(4000)
SET @.w = 'SELECT.....FROM ['
IF (@.server IS NOT NULL) BEGIN SET @.w = @.w + @.server + '].[' END
SET @.w = @.w + @.database + ']..[' + @.table + ']...(rest of query)'
EXECUTE sp_executesql @.w
Cheers,
Peter.
Peter Hyssett
"Bruno" wrote:

> Dear all
> I got a problem that I would like to use a dynamic statement to query data
> from a linked server (it is not guaranteed that the used database remains
on
> this server or the Test-Database would be renamed).
> I tried to use the OPENQUERY-Functionality or to set up a SP which uses
> Parameters
> Example (SP with OPENQUERY)I:
> ALTER PROCEDURE sp_GetParameterTable
> @.LnkSrv NVARCHAR(30), @.LnkTbl NVARCHAR(30)
> AS
> SELECT
> *
> FROM
> OPENQUERY
> (
> LTRIM(@.LnkSrv), -- LinkedServer
> '
> SELECT
> *
> FROM ' +
> LTRIM(@.LnkTbl)
> + '
> WHERE
> (X_TYPE = ''XYZ'')
> ORDER BY
> CONVERT(FLOAT,X_KEY) DESC
> '
> )
> ->> it won't work !!
> the other way to use a a SELECT Statement and a Function to get the Table
> won't also not work.
>
> SELECT
> TOP 1
> CONVERT(FLOAT,X_KEY) AS LastUsedKey
> FROM
> lokalserver.dbo.fn_Test
> (
> LinkedServer,
> DataBase,
> Table
> )
> WHERE
> (X_TYPE = 'XYZ')
> ORDER BY
> CONVERT(FLOAT,X_KEY) DESC
> -->> Function
> ALTER FUNCTION fn_Test
> (
> @.SrvName NVARCHAR(20),
> @.DBName NVARCHAR(20),
> @.tblName NVARCHAR(35)
> )
> RETURNS NVARCHAR(80)
> AS
> BEGIN
> DECLARE @.DBRef NVARCHAR(80)
> SET @.DBRef = LTRIM(@.SrvName) + '.'
> + LTRIM(@.DBName ) + '.dbo.'
> + LTRIM(@.tblName )
> RETURN(LTRIM(@.DBRef))
> END
> This will also not working.
> Could anybody help me in this context? Any Help will be appreciated.
> Youry,
> Bruno

Dynamic query: EXEC - Need to get a return value from query

I use dynamic SELECT statement trying to achive the followng:

DECLARE @.table varchar(50)

DECLARE @.ColumnA varchar(50)

DECLARE @.Result float

SET @.table = 'TABLE_A'

SET @.ColumnA = 'COLUMN_1'

SET @.SQL = ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

EXEC (@.SQL)

I need to assign the outcome of this statement to the @.Result variable. It does not work because of different contexts for @.Result. It would run if I do

SET @.SQL = 'DECLARE @.Result float '

SET @.SQL = @.SQL + ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

But in this case @.Result is not 'visible' outside EXEC statement.

One way to solve this is to use INSERT statement into temp table and then read result from the temp table.

Are there any more elegant solutions?

Thanks!

Sorry, but the code in EXEC() is considered to be its own batch, so you will need to persist it in a table, and temp tables are usually used for this. BTW, what did you need to do with the @.Result value? You may be able to put that in the same EXEC() code.

Thanks, Dean

|||

There are different ways to execute dynamic sql. The one that you need is sp_execsql. It allows you to declare parameters -- even output parameters. A key to remember is that an output parameter must be declared as output twice, in much the same way that an output parameter is declared both inside a stored procedure and on the exec line.

Your code should look like:

Declare @.result float

SET @.SQL = @.SQL + ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

exec sp_execsql @.sql, N' @.Result float Output', @.Result output

The command wants NVarchar() parameters. Make sure @.sql is declared NVarchar() and include the N on the literal declaring the parameter. You can have multiple parameters if needed.

Dynamic query, local cursor variable and global cursors

Hi all.

I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.

So, first things first: let me explain what I need to do. I am

designing a web application that will allow users to consult info

available in a SQL2000 database. The user will enter the search

criterea, and hopefully the web page will show matching results.

The problem is the results shown aren't available per se in the DB, I

need to process the data a bit. I decided to do so on the SQL Server

side, though the use of cursors. So, when a user defines his search

criteria, I run a stored procedure that begins by building a dynamic

sql query and creating a cursor for it. I used a global cursor in order

to do so. It looked something like this:

SET @.sqlQuery = ... (build the dinamic sql query)

SET @.cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor

This works fine, if there's only one instance of the

stored procedure running at a time. Should another user connect to the

site and run a search while someone's at it, it'll fail due to the

atempt to create a cursor with the same name.

My first thought was to make the cursor name unique, which led me to:

...

SET @.cursorName = 'myCursor' + @.uniqueUserID

SET @.cursorQuery = 'DECLARE '+ @.cursorName + 'CURSOR FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

...

The problem with this is that I can't do a FETCH NEXT FROM @.cursorName since

@.cursorName is a char variable holding the cursor name, and not a

cursor variable. So to enforce this unique name method the only option

I have is to keep creating dynamic sql queries and exucting them. And

this makes the sp a bitch to develop and maintain, and I'm guessing it

doesn't make it very performant.

So I moved on to my second idea: local cursor variables. The problem with

this is that if I create a local cursor variable by executing a dynamic

query, I can't extract it from the EXEC (or sp_executesql) context, as

it offers no output variable.

I guess my concrete questions are:

Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?

Anybody sees another way arround this?Thanks in advance,

Carlos

First off, let me just say that I kind of hate myself for the answer I am going to give you, because almost certainly the processing you are trying to do with a cursor in SQL could be done easier/better/faster/etcer outside of T-SQL, and you would be far happier with the final result.

On the other hand, I am pretty sure there is a way to do this, using sp_executesql by passing a parameter of type cursor to the proc:

declare @.query nvarchar(max), @.number int, @.mainCursor cursor

set @.query = ' set @.cursor = cursor for select 1 as number
open @.cursor'

exec sp_executesql @.query,N'@.cursor cursor output',@.mainCursor output

fetch next from @.maincursor into @.number
select @.number

Good luck with this, but seriously consider doing this outside of T-SQL :)

|||Hey Louis.

I can only say I was amazed to see that your piece of code worked,

since I had tried about the same thing a while ago and it didn't work.

So I did a little digging to see what the difference was between your

implementation and mine. And then I discovered something odd: if you

put the OPEN @.cursor after the sp_executesql command, instead of in it, you get an error saying that your variable has no cursor allocated to it. Go figure.

Well, I guess this is part of why you're telling me to give up T-SQL.

Believe me, I'm no masochist. I know this would be much easier if I did

it on the webserver's side, where I have a beautiful JVM eager to do

the job. But I have to disagree when you say it would be faster.

The trouble is that I have to go through a great amount of data to

display but a few lines of result to the user. The overhead involved in

transfering all this data to another system is just too great (it's a

web application, so the time scale is very short: a few seconds will be

enough to hamper it). So I guess I'll have to live with it, right?

Anyways, many thanks for your help. Problem solved. Moving on.

Carlos

Dynamic Query!

I am trying to create a stored procedure containing a dynamic query.
I am still new to using conditionals in sql, so any help to
get this query running would be appreciated!!!
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
IF @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
AND start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
ENDHi
Just check if this solves the purpose
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
CASE WHEN @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
END
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"AJ" wrote:

> I am trying to create a stored procedure containing a dynamic query.
> I am still new to using conditionals in sql, so any help to
> get this query running would be appreciated!!!
> CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
> AS
> BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END|||Well you could approach it very simplistically and just replace all your
different IF cases with OR operations (because that's what they really
are) like this:
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE (@.name IS NOT NULL and first_name + ' ' + last_name LIKE @.name)
OR (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR (start_dt BETWEEN @.from_dt AND @.to_dt)
You could shuffle the WHERE clause around a bit but chances are the
query optimiser will come up with the same plan for the majority of the
variations so you may as well stick to something that you understand and
that's readable (so those who maintain the system after you aren't
bamboozled by your code).
I changed your "<= AND >=" bits to "BETWEEN" because it's a little more
readable IMHO. Also I noticed you left off the '@.' symbol on a couple
references to your proc parameters in the WHERE clause (to_dt & from_dt).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:

>I am trying to create a stored procedure containing a dynamic query.
>I am still new to using conditionals in sql, so any help to
>get this query running would be appreciated!!!
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
>END
>|||Hmmm... The CASE statement is wrong. I think you mean
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
THEN start_dt >= @.from_dt AND start_dt <= @.to_dt
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
But I'm not sure that would work even. BOL says the bit after THEN can be a
ny valid SQL expression but I don't know if "x LIKE y" or "a >= x and a <= b
" are valid in this context (even though they just resolve to a boolean, whi
ch I guess is a valid SQL e
xpression) - never tried that before. At the very least it's a little unort
hodox. Typically a CASE is used to return a specific value to compare somet
hing to like this
WHERE MyCol = (CASE
WHEN a THEN SomeVal
WHEN b THEN SomeOtherVal
ELSE SomeCatchAllVal
END)
Also, the middle case is redundant because it's the same result as the ELSE
case. You could simply write it as
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
However, you could get rid of the CASE statement entirely by saying
WHERE first_name + ' ' + last_name LIKE @.name
OR start_dt BETWEEN @.from_dt AND @.to_dt
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chandra wrote:

>Hi
>Just check if this solves the purpose
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> CASE WHEN @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
> start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END
>END
>
>|||Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where
clause but if no value is passed into the proc for each specific
parameter then a default value will be used, for each parameter, such
that it won't limit the resultset at all (ie. any @.name string, the min
@.from_dt value and the max @.to_dt value for the datatypes you chose).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:

>Hi all, so far i have adopted the following approach.
>If no parameters are supplied i want all records to be selected.
>At the moment this isn't catered for in the query below.
>My overall logic is:
>If @.name is provided filter results with @.name
>If @.from_dt is provided filter results with @.from_dt
>If @.name and @.from_dt are provided filter with both.
>@.if no parameters are provided just select all records.
>Can anyone give some modifications to this query to achieve this?
>Thanx...
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> (@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
> OR
> (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
>@.from_dt AND @.to_dt)
> OR
> (@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
> ORDER BY
> last_name
>|||One small caveat to Mike's excellent suggestion - the technique he proposes
requires that none of the columns (first_name, last_name, start_dt, and
end_dt columns) be nullable. If any of the rows contains nulls in one or
more of those columns, then you will not match that row. Since you did not
post any DDL, we do not know the details of the table being searched, so
this may or may not apply in your case. In the future, please post DDL and
sample data so you have the best chance of receiving the most complete
answer possible.
Erland Sommarskog has a great article about dynamic search criteria at:
http://www.sommarskog.se/dyn-search.html
ITHT
Jeremy Williams
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23xxsVZmaFHA.3400@.tk2msftngp13.phx.gbl...
Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where clause
but if no value is passed into the proc for each specific parameter then a
default value will be used, for each parameter, such that it won't limit the
resultset at all (ie. any @.name string, the min @.from_dt value and the max
@.to_dt value for the datatypes you chose).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
AJ wrote:
Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||No, SQL has a CASE expression **not** a CASE statement. Big
difference! Expressionds return a value: they do not control flow of
control.|||Exactly what I was getting at (did you read my whole post?).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
--CELKO-- wrote:

>No, SQL has a CASE expression **not** a CASE statement. Big
>difference! Expressionds return a value: they do not control flow of
>control.
>
>

Dynamic query with SQL statement longer than 8000 chars

I have a stored procedure generating a dynamic SQL that runs against Analysi
s
Services. The statement can be quite complicated and can be longer than 8000
.
I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how man
y
@.strSQL variables I'll need, I was wondering if anybody has a better solutio
n
for this.
If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
that resembles a procedure in VB? I don't think UDFs would work here.
Thanks in advance for any help,
Carmen.Carmen (Carmen@.discussions.microsoft.com) writes:
> I have a stored procedure generating a dynamic SQL that runs against
> Analysis Services. The statement can be quite complicated and can be
> longer than 8000. I know I can use "exec (@.strSQL1 + @.strSQL2)" but
> since I don't know how many @.strSQL variables I'll need, I was wondering
> if anybody has a better solution for this.
Upgrade to SQL 2005, where you can use varchar(MAX) and you are free
of all hassle.

> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
If it's that bad, it may be better to build the query that has better
string capabilities, for instance VB.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Which parts of your SQL statement are dynamic? Is it possible that some of
your dynamic SQL could be done another way? Perhaps using a temp table in
place of a list for an in clause, or splitting parts of the dynamic SQL off
into views? I can only guess at what you are doing but I have to believe
that if you are getting over 16000 characters in your SQL that there must be
ways to simplify it and make the final SQL more concise.
Sometimes we get so concerned with making the current process work as
originally designed, and we overlook that there are flaws in the design.
Once we redesign the process to remove those flaws we find out that what we
thought was a problem was really only a symptom of another problem. This
may or may not be the case here, but it is worth considering nonetheless.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:A7D9B446-D779-4A8B-8823-5701137660C3@.microsoft.com...
> I have a stored procedure generating a dynamic SQL that runs against
Analysis
> Services. The statement can be quite complicated and can be longer than
8000.
> I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how
many
> @.strSQL variables I'll need, I was wondering if anybody has a better
solution
> for this.
> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
> Thanks in advance for any help,
> Carmen.sql

Dynamic Query with Multi-valued parameter

I'm having problems getting a dynamic query with a multi-valued parameter. I
have a report parameter, defined as multi-value, called deployment_id. I
want to have a dynamic query built with it (because I am eventually going to
also send in a parameter for the WHERE clause).
This works:
SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name
FROM o_dpl_communication C INNER JOIN
o_dpl_recipient R ON C.recipient_id = R.recipient_id
WHERE C.deployment_id IN (@.deployment_id)
This does not:
="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name " &
"FROM o_dpl_communication C INNER JOIN " &
"o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
"WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
Anyone know how I can fix this dynamic query? Thanks!
StephanieRead the thread Dynamic Conditional report parameters. I show how to do this
... kindof. Because you need single quotes this is not easy (unless your
parameter list is integer, in which case it is quite easy). Anyway, read the
thread and see if it helps you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||try to use:
Join(Parameters!deployment_id.Value, ", ")
the .value return an array, the Join keyword convert the array into a string
with a comma as a separator
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||First of all, are you imbedding code directly in the report? that is not
good, since you have to redeploy the whole report should the code need to be
changed. Make a stored procedure instead and pass the list in as a
varchar(255).
Once inside the sproc, all you have to do is parse through the list in a
while loop and create output into a temp table that you can then filter or
output any way you want.
this is way more efficent on the sql server, and will make life easier in
the long run!
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||That worked perfectly, thanks very much!
Stephanie
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||Carl,
Thanks for the reply. I knew I could use a stored proc but I needed this to
work on multiple databases. I'm sending in a report parameter of the
database name that I want to report on and I don't want the stored proc to be
in each of the databases.
Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
Stephanie
"Carl Henthorn" wrote:
> First of all, are you imbedding code directly in the report? that is not
> good, since you have to redeploy the whole report should the code need to be
> changed. Make a stored procedure instead and pass the list in as a
> varchar(255).
> Once inside the sproc, all you have to do is parse through the list in a
> while loop and create output into a temp table that you can then filter or
> output any way you want.
> this is way more efficent on the sql server, and will make life easier in
> the long run!
> "Jeje" wrote:
> > try to use:
> > Join(Parameters!deployment_id.Value, ", ")
> >
> > the .value return an array, the Join keyword convert the array into a string
> > with a comma as a separator
> >
> >
> > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > I
> > > have a report parameter, defined as multi-value, called deployment_id. I
> > > want to have a dynamic query built with it (because I am eventually going
> > > to
> > > also send in a parameter for the WHERE clause).
> > >
> > > This works:
> > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name
> > > FROM o_dpl_communication C INNER JOIN
> > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > WHERE C.deployment_id IN (@.deployment_id)
> > >
> > > This does not:
> > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name " &
> > > "FROM o_dpl_communication C INNER JOIN " &
> > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > >
> > > Anyone know how I can fix this dynamic query? Thanks!
> > >
> > > Stephanie
> >
> >|||Thanks, Bruce. That's just what I was looking for. And I appreciated seeing
the additional information on the parameters, ie. Value(0), Count, etc.
Stephanie
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||thats cool. In that particular case, i would place the dynamic sql inside the
loop and passed in the value. I am glad you have a working solution! :-)
"Stephanie" wrote:
> Carl,
> Thanks for the reply. I knew I could use a stored proc but I needed this to
> work on multiple databases. I'm sending in a report parameter of the
> database name that I want to report on and I don't want the stored proc to be
> in each of the databases.
> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
> Stephanie
>
> "Carl Henthorn" wrote:
> > First of all, are you imbedding code directly in the report? that is not
> > good, since you have to redeploy the whole report should the code need to be
> > changed. Make a stored procedure instead and pass the list in as a
> > varchar(255).
> > Once inside the sproc, all you have to do is parse through the list in a
> > while loop and create output into a temp table that you can then filter or
> > output any way you want.
> > this is way more efficent on the sql server, and will make life easier in
> > the long run!
> >
> > "Jeje" wrote:
> >
> > > try to use:
> > > Join(Parameters!deployment_id.Value, ", ")
> > >
> > > the .value return an array, the Join keyword convert the array into a string
> > > with a comma as a separator
> > >
> > >
> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > > I
> > > > have a report parameter, defined as multi-value, called deployment_id. I
> > > > want to have a dynamic query built with it (because I am eventually going
> > > > to
> > > > also send in a parameter for the WHERE clause).
> > > >
> > > > This works:
> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name
> > > > FROM o_dpl_communication C INNER JOIN
> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > > WHERE C.deployment_id IN (@.deployment_id)
> > > >
> > > > This does not:
> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name " &
> > > > "FROM o_dpl_communication C INNER JOIN " &
> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > > >
> > > > Anyone know how I can fix this dynamic query? Thanks!
> > > >
> > > > Stephanie
> > >
> > >|||I'm pretty sure it only works because the data type of the field is integer.
If it had been string it would not have worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
> thats cool. In that particular case, i would place the dynamic sql inside
> the
> loop and passed in the value. I am glad you have a working solution! :-)
> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed this
>> to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then filter
>> > or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into a
>> > > string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am eventually
>> > > > going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >|||sure
in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:
'toto', 'tata', 'tutu'
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
> I'm pretty sure it only works because the data type of the field is
> integer. If it had been string it would not have worked.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql inside
>> the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>|||Good point.
Bruce
"Jeje" <willgart@.hotmail.com> wrote in message
news:4ED05EA4-A55B-480A-BFBB-6B863080B026@.microsoft.com...
> sure
> in case of an array of string the code is different
> something like:
> "'" & Join(Parameters!deployment_id.Value, "', '") & "'"
> single quote ' added in the join clause + single quote ' added before and
> after the Join clause produce:
> 'toto', 'tata', 'tutu'
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
>> I'm pretty sure it only works because the data type of the field is
>> integer. If it had been string it would not have worked.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql
>> inside the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code
>> > need to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list
>> > in a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life
>> > easier in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>>
>|||Bruce, cannot locate the thread, believe that info will be very helpful to me.
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||Go to Google, click on groups from the down arrow so you search groups then
search on this:
Dynamic Conditional report parameters
You will see it then.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Z-Will" <Z-Will@.discussions.microsoft.com> wrote in message
news:3EFFC67E-1254-4B64-90C7-264C3C43C36B@.microsoft.com...
> Bruce, cannot locate the thread, believe that info will be very helpful to
> me.
> "Bruce L-C [MVP]" wrote:
>> Read the thread Dynamic Conditional report parameters. I show how to do
>> this
>> ... kindof. Because you need single quotes this is not easy (unless your
>> parameter list is integer, in which case it is quite easy). Anyway, read
>> the
>> thread and see if it helps you.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > I'm having problems getting a dynamic query with a multi-valued
>> > parameter.
>> > I
>> > have a report parameter, defined as multi-value, called deployment_id.
>> > I
>> > want to have a dynamic query built with it (because I am eventually
>> > going
>> > to
>> > also send in a parameter for the WHERE clause).
>> >
>> > This works:
>> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name
>> > FROM o_dpl_communication C INNER JOIN
>> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > WHERE C.deployment_id IN (@.deployment_id)
>> >
>> > This does not:
>> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name " &
>> > "FROM o_dpl_communication C INNER JOIN " &
>> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> >
>> > Anyone know how I can fix this dynamic query? Thanks!
>> >
>> > Stephanie
>>

Dynamic Query View?

This summary is not available. Please click here to view the post.

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic Query Report Server 2005

Because of several reasons i have to set the query statement of a report
dynamically. Does anybody know if that is possible just before rendering
a report? I use the web Services interface for accessing the reports.
thanks for any suggestions
MarkusYou cannot modify the query statement through e.g. SOAP. You would need to
republish the report. But is this really necessary? Did you look into using
an expression-based query commandtext (which is already available in
RS2000)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Markus" <markus@.m-s-wunderlich.de> wrote in message
news:cu36m2$dq9$1@.online.de...
> Because of several reasons i have to set the query statement of a report
> dynamically. Does anybody know if that is possible just before rendering a
> report? I use the web Services interface for accessing the reports.
> thanks for any suggestions
> Markus|||Another thing you could do is to write a Stored Procedure which accepts a
parameter and issues one of several queries, based on the parameter, or
perhaps generates the query string and executes it via sp_executesql
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Markus" <markus@.m-s-wunderlich.de> wrote in message
news:cu36m2$dq9$1@.online.de...
> Because of several reasons i have to set the query statement of a report
> dynamically. Does anybody know if that is possible just before rendering a
> report? I use the web Services interface for accessing the reports.
> thanks for any suggestions
> Markus|||Robert, what do you mean "expression-based query commandtext"?
Here is my problem - I need to pass in a parameter with multiple values.
What is the recommended way to accomplish this?
The only suggestion I've gotten so far is to create a user based
function on the reporting SQL server that can parse my parameter string
into a table. I don't wish to do this if there is an easier way.
So then I think, hey! maybe I can generate my own sql then pass it to
the report. Wrong. Apparently I can define the query if I'm creating a
data driven subscription but not if I just want to pass it to an
existing Report? This makes absolutely no sense to me.
Does anyone have a recommendation for me?
thanks,
Ian Stallings
Robert Bruckner [MSFT] wrote:
> You cannot modify the query statement through e.g. SOAP. You would need to
> republish the report. But is this really necessary? Did you look into using
> an expression-based query commandtext (which is already available in
> RS2000)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Markus" <markus@.m-s-wunderlich.de> wrote in message
> news:cu36m2$dq9$1@.online.de...
>>Because of several reasons i have to set the query statement of a report
>>dynamically. Does anybody know if that is possible just before rendering a
>>report? I use the web Services interface for accessing the reports.
>>thanks for any suggestions
>>Markus
>
>|||By expression based he means the following. Go to the generic view of the
query designer (hover over the buttons to the right of the ... to find the
one to click on). You can do either of these two things:
select * from sometable
or you can put in an expression:
="select * from sometable"
I use this technique for having a parameter specify my order by but you
could use this for your needs as well. Note that you have to make everything
perfect for this to work. I usually first just have a report with parameters
and a single textbox on the report (no query to start off with). I assign
the expression to the textbox and test it out until I see the proper SQL
string.
Here is an example for using a parameter
="SELECT * FROM sometable order by " & parameters!SortBy.value
The point here is that you are dynamically creating the SQL statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ian Stallings" <jovian_moon@.hotmail.com> wrote in message
news:%23Lm1WnjNFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Robert, what do you mean "expression-based query commandtext"?
>
> Here is my problem - I need to pass in a parameter with multiple values.
> What is the recommended way to accomplish this?
> The only suggestion I've gotten so far is to create a user based
> function on the reporting SQL server that can parse my parameter string
> into a table. I don't wish to do this if there is an easier way.
> So then I think, hey! maybe I can generate my own sql then pass it to
> the report. Wrong. Apparently I can define the query if I'm creating a
> data driven subscription but not if I just want to pass it to an
> existing Report? This makes absolutely no sense to me.
>
> Does anyone have a recommendation for me?
> thanks,
> Ian Stallings
>
>
> Robert Bruckner [MSFT] wrote:
> > You cannot modify the query statement through e.g. SOAP. You would need
to
> > republish the report. But is this really necessary? Did you look into
using
> > an expression-based query commandtext (which is already available in
> > RS2000)?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Markus" <markus@.m-s-wunderlich.de> wrote in message
> > news:cu36m2$dq9$1@.online.de...
> >
> >>Because of several reasons i have to set the query statement of a report
> >>dynamically. Does anybody know if that is possible just before rendering
a
> >>report? I use the web Services interface for accessing the reports.
> >>thanks for any suggestions
> >>
> >>Markus
> >
> >
> >|||Just a follow up, I have fixed this problem. I ended up using a user
defined function to parse the parameter (which is passed in as '1,2,3'
.. etc) and then return a table with the datatypes I need, I then query
against that and return a recordset that I use in my dataset query.
Here are more details in case anyone comes searching later:
http://weblogs.asp.net/jmoon/archive/2005/04/01/396649.aspx
- Ian Stallings
Ian Stallings wrote:
> Robert, what do you mean "expression-based query commandtext"?
>
> Here is my problem - I need to pass in a parameter with multiple values.
> What is the recommended way to accomplish this?
> The only suggestion I've gotten so far is to create a user based
> function on the reporting SQL server that can parse my parameter string
> into a table. I don't wish to do this if there is an easier way.
> So then I think, hey! maybe I can generate my own sql then pass it to
> the report. Wrong. Apparently I can define the query if I'm creating a
> data driven subscription but not if I just want to pass it to an
> existing Report? This makes absolutely no sense to me.
>
> Does anyone have a recommendation for me?
> thanks,
> Ian Stallings
>
>
> Robert Bruckner [MSFT] wrote:
>> You cannot modify the query statement through e.g. SOAP. You would
>> need to republish the report. But is this really necessary? Did you
>> look into using an expression-based query commandtext (which is
>> already available in RS2000)?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Markus" <markus@.m-s-wunderlich.de> wrote in message
>> news:cu36m2$dq9$1@.online.de...
>> Because of several reasons i have to set the query statement of a
>> report dynamically. Does anybody know if that is possible just before
>> rendering a report? I use the web Services interface for accessing
>> the reports.
>> thanks for any suggestions
>> Markus
>>
>>

Dynamic Query question

I am using the BOL walkthrough and have come to the Dynamic Query section.
I type in the following into the generic query builder...
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
But it does not work... Can anyone please assist?If you mean that the fields list is not populated, yes, for query
expressions you'll need to populate the fields list manually. Another
approach is to first type in the static version of the query (select
FirstName, LastName, Title from Employee), run the query, switch to layout
pane, switch back to data pane, and then replace the static version with the
expression-based version you have below. You will then see the fields list.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bing" <.> wrote in message news:O$HyaHpfEHA.3676@.TK2MSFTNGP12.phx.gbl...
> I am using the BOL walkthrough and have come to the Dynamic Query section.
> I type in the following into the generic query builder...
>
> ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> But it does not work... Can anyone please assist?
>|||if DepartmentID is numeric try:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE
(DepartmentID = " &
int(Parameters!Department.Value) & ")") & " ORDER BY
LastName"
if DepartmentID is string try:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE
(DepartmentID = '" &
Parameters!Department.Value & "')") & " ORDER BY LastName"
>--Original Message--
>I am using the BOL walkthrough and have come to the
Dynamic Query section.
>I type in the following into the generic query builder...
>
>="SELECT FirstName, LastName, Title FROM Employee" &
>IIf(Parameters!Department.Value = 0,""," WHERE
(DepartmentID = " &
>Parameters!Department.Value & ")") & " ORDER BY LastName"
>But it does not work... Can anyone please assist?
>
>.
>

dynamic query problem

I make a sp in mt sql server,

the SP get one parameter, its the WHERE,
and then I execute the query,

here is my SP:
ALTER PROCEDURE [dbo].[rptEventToPsy]
@.strSQL nvarchar(4000)=''
as
DECLARE @.SQLString NVARCHAR(4000);
SET @.SQLString = N'SELECT
dbo.tbl_CA_meeting.userID, dbo.tblUsersName.OrdName, COUNT(dbo.tbl_CA_event.itemInPackageID) AS Expr1, dbo.tbl_CA_itemInPackage.itemInPackageName, dbo.tbl_CA_package.packageName FROM dbo.tbl_CA_meeting INNER JOIN
dbo.tbl_CA_event ON dbo.tbl_CA_meeting.eventID = dbo.tbl_CA_event.eventID
INNER JOIN
dbo.tbl_CA_itemInPackage ON dbo.tbl_CA_event.itemInPackageID = dbo.tbl_CA_itemInPackage.itemInPackageID
INNER JOIN
dbo.tbl_CA_package ON dbo.tbl_CA_itemInPackage.packageID = dbo.tbl_CA_package.packageID
INNER JOIN
dbo.tblUsersName ON dbo.tbl_CA_meeting.userID = dbo.tblUsersName.UserId
INNER JOIN
dbo.tbl_CA_mishmeret ON dbo.tbl_CA_meeting.mishmeretID = dbo.tbl_CA_mishmeret.mishmeretID ';
SET @.SQLString = @.SQLString + @.strSQL ;
SET @.SQLString = @.SQLString + '
GROUP BY
dbo.tbl_CA_meeting.userID,
dbo.tblUsersName.OrdName,
dbo.tbl_CA_itemInPackage.itemInPackageName,
dbo.tbl_CA_package.packageName';
EXEC sp_ExecuteSql @.SQLString
return;

my problem is in the Reports, I cant to connect the data to the fileds, (the value is empty)

how can I do it?First, your stored procedure is very susceptible to SQL injection. You really need to constrain the input more. There are several articles on the web that will help you. You can accomplish the same result by using an expression for the SQL statement in the report and write some functions to check the input values.sql

Dynamic Query Problem

I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.

If I try to run it with QA, I get the following message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FCST'.
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...

However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.

What could I be missing?

UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
Dec_07=Dec_fcst
FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT,
Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst,
Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst
FROM PLAN_SHIP INNER JOIN BOD_HEADER
ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX
WHERE (SCEN_ID = 1) AND ([Year] = 2007)
) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
WHERE OPSPLAN.SRCPLAN = 'SHIP'I'd sic the SQL Profiler on this fella. My suspicion is that the UPDATE is being mis-parsed, possibly because of a syntax error within the previous SQL statement. Profiler ought to give you some clues if that is the case. No outright answers, just clues, but that's more than you have now.

-PatP|||OK, I read up on it in BOL, and figured out how to get profiler running.
I found the line where that particular statement is executing.
I'm looking at StmtStarting and StmtCompleted. Is there something in
particular that I should be looking for?|||Since the string "FCST" does not appear independently in your code, but only in conjuction with a month and an underscore character, I'd say that somewhere and underscore character is being dropped.

Set the dynamic sql statement to print rather than execute, and bump up the Max characters setting in the Query Analyzer Options. Then see what code is actually being executed.|||Yeah, that "FCST" was throwing me, too. All of the instances in my string
are "fcst" not "FCST". Anyway, after pouring through the sproc over and over, I found a PRINT statement that was causing the completely valild statement to display under the error message. It wasn't the cause at all,
it was a previous statement. After I eliminated that, it was easy to narrow it down.

Thanks

Dynamic Query in View

Can I use variable name in the view? because the result set is depend on
different database not the same database, so I am wondering if I can use
this as a dynanice query in the view then my problem would be solved.
Thanks in advance
Views cannot have parameters, but table-valued user-defined functions can.
They don't necessarily have the same performance as views, but this sounds
like what you want. You can do subqueries on TVF too, unlike resultsets that
are returned from stored procedures.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Rogers" <naissani@.hotmail.com> wrote in message
news:uF1y3UlLGHA.3100@.tk2msftngp13.phx.gbl...
> Can I use variable name in the view? because the result set is depend on
> different database not the same database, so I am wondering if I can use
> this as a dynanice query in the view then my problem would be solved.
> Thanks in advance
>