how to use Contains function on sql parameters?If Sql parameter has space then its not working else working.
e.g
If @searchParam='serachtext' then its working but if its @searchParam='serach text' then not working
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
I have a report that i want to beable to do some dynamic paramaters on it. I have a start and end date of when they want to run the report but i also have a mulivalue list box for users filtering for users. I want to add a supervisor filter on form also. But I have a problem.
I want to allow the users to select a supervisor and filter for all employees under that, or allow them to select a set of users and allow them to filter for that also. Also i would like when they select no supervisor or user it just filters for the dates and picks all up all the users.
I thought i could do something like in access like this Like UserName & "*"
but it doesnt work.
Here is my dataset code i am using currently:
SELECT U.Name, A.row_date, A.split, A.SumOfti_stafftime, A.AHT, A.AvgACW, A.AvgACD, A.AvailTime, A.SplitSkill_Incalls, A.SplitSkill_Outcalls, A.SumOfacdtime,
A.SumOfti_othertime, A.SumOfacwtime, A.SumOfti_auxtime0, A.SumOfti_auxtime1, A.SumOfti_auxtime2, A.SumOfti_auxtime3, A.SumOfti_auxtime4,
A.SumOfti_auxtime5, A.SumOfti_auxtime6, A.SumOfti_auxtime7, A.SumOfti_auxtime8, A.SumOfti_auxtime9, U.Sup
FROM tblAvayaDaily AS A RIGHT OUTER JOIN
tblUsers AS U ON A.logid = U.[Avaya ID]
WHERE (A.split = 1651) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR
(A.split = 1655) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR
(A.split = 1653) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd)
I am currently working on a project that involves creating dynamic insert and update statements for dynamically created tables. The tables I am creating the statements for could have up to 1000 columns.
Obviously building the entire insert/update SQL string dynamically in VB.Net and sending it to the Sql server leaves the system vulnerable to injection attack or error caused by single quotes in strings.
I am using the Patterns and Practices Data Application block for my data access.
What I am considering is creating the dynamic sql as a statement with the columns names and values in as parameters (@XXXXX) and then looping through my list of columns and values and adding them as by AddInParameters on the DbCommand.
- Can it handle such large amount of parameters at all?
- What kind of structure does ADO.net send to the SQL server? i.e. Will using so many parameters mean that the data communicated to the SQL server from the webserver by the parameter method would be much larger than the substituted dynamic sql string?
- How does ADO.NET/SQL Server 2005 perform with such large amounts of parameters on a DBCommand?
Any advice as to whether this is a viable way to proceed would be much appreciated.
Oh yeah... I am using VB.NET in VIsual Studio 2005 with a SQL Server 2005 DB.
Thanks
Mark
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Here is he issue.
* We have a DAL that generates all SQL dynamically out of a nobject model. Standard very powerfull O/R mapper.
* In the DAL, for CRUD operations, we generate the statements dynamically. As an example, let's take INSERT.
* The insert is generated ONCE, with parameters, and cached. For every reuse, the parameters are replaced (in value), and the whole thing commited.
I see hte following negative: I can not easily batch multiple inserts. Parameters have to be unique per batch. So, if I want to batch two inserts, I need two sets of parameters.
Alternative:
Instead of generating the SQL with parameters, we generate the SQL as a string ready to be inserted for / with a String.Format, and then I encode the parameters and make one SQL String out of this. Now, please - don't say "sql injection", we are not that stupid, the layer handles this already, properly encoding all dangerous values.
With this approach, the SQL statement would be a string and not use any parameter. As a result, I could batch them up as much as I want (ok, up to a certain string size). I need to keep parameters around anyway (for blobs etc.), but most objects do not have blobs, and the SQL is prettty small. This small SQL could be batched significantly (100 statements per batch, propably mode) and be submitted to the database. As a result, the round trips to the databae would go down.
Now, my question is - which of the two approaches is more advisable, from a performance point of view? Again, stuff like SQL injection and ease of handling are totally irrelevant - the SQL never leaves the DAL and is generated in there, and we will go through a lot of complexitiy for higher performance.
Normally I would say batching should be better. SQL Server can auto-parameterize the statements (reusing the query plan), and / but the network round trips are the larger issue here.
Any comments on this?
Hi All,
I'm trying to write a formula (actually, four) which will be used in Panorama NovaView to allow a user to see Revenue (on rows) by Previous Month (Current Year), Previous Month (Previous Year), Current YTD (ending at month in question), Previous YTD (ending at month in question) (on columns -- not necessarily in that order).
Basically, when run anytime during November 2005, the output would look like:
2004 Total | Oct 2004 | 2005 Total | Oct 2005
$ 100,000 | 15,000 | 120,000 | 17,500
The Dimension I'm working with is [Calendar Year], with levels: (All), [Calendar Year], [Calendar Month], [Calendar Week].
I've been focusing on the formula for the first column, since it is the most complex (I think). Here's the jist of what I am trying to do:
SELECT Measures.Revenue ON ROWS,
<<Last Year>>.January : <<Last Year>>.<<LastMonth>> ON COLUMNS
FROM SalesCube
I believe some combination of PeriodsToDate(), LastPeriod() and/or <<Current Year>>.PrevMember & <<Current Month>>.PrevMember is what I need, but I just can't get the syntax right.
Any help would be appreciated.
Thanks,
Ian Field
SD Union-Trib
Good day,
I have an issue on constructing dynamic WHERE conditions that use OPTIONAL parameters.
SP_SOMETHING (
1) @DateFrom datetime,
@DateTo datetime,
2) @Param1 char(8),
3) @Param2 char(3),
4) @Param3 tinyint
)
I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together)
1 2 3 4
/ x x x
x / x x
x x / x where x = not supplied
/ = supplied a value
(and so the list continues)
Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements.
thank you
Hello all,
I have a winform application, where in we get the parameters from the reporting service and we display them in a panel for user's to input their criteria. So far everything worked good, but now we have a requirement wherein dynamic functionality is needed.
For Example:
Based on a selection of a combobox value, we want the other control to be enabled or disabled. Also we want default value of a control to be calculated based on another control's value like .. if user enters value "1" in textbox1 then texbox 2 should have default value of "6". (Textbox1 + 5).........and mix and match of such capabilities.
The Question is........IS IT POSSIBLE??? and if yes...HOW!!!
Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select €œselect all€? option from the parameter list all 20 parameters value should be false.
I will be very thankful.
Regards,
Faisal Saleem
Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.
Case 1:
-------------
AdomdCommand cmd = new AdomdCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @var0 as [var] " +
" UNION SELECT @var1 as [var] " +
" UNION SELECT @var2 as [var]) AS [vartable]) AS t";
Case 2
-------------
AdomdCommand cmd = new AdomdCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @var0 as [var] ";
for(int i=1; i<3; i++)
{
cmd.CommandText = cmd.CommandText +
"UNION SELECT @var" + i.ToString() + " as [var] ";
}
cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";
Mary
I have a stored procedure that uses a dynamic order by statement. This statement works okay until I try to select ticket's by userEmail which is passed in to my stored procedure as a parameter. Here is the code that works for my dynamic sort order:
CREATE PROCEDURE [dbo].[SelectAllTickets]
@SortOrder varchar(250)
AS
SET NOCOUNT ON
Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID,
b.typeID, b.typeName,
c.userID, c.UserFirstName,
f.statusID, f.statusName
FROM Tickets a
LEFT OUTER JOIN Type b ON b.typeID = a.typeID
LEFT OUTER JOIN Users c ON c.userID = a.userID
LEFT OUTER JOIN Status f ON f.statusID = a.statusID
ORDER BY ' + @SortOrder)
I modied this procedure to create one in which I select tickets based on the userEmail as a criteria as well.. this one fails due to Incorrect syntax near the keyword 'BY'
CREATE PROCEDURE [dbo].[SelectTicketByUser]@SortOrder varchar(250),@userEmail varchar(50)
AS SET NOCOUNT ON Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID, b.typeID, b.typeName, c.userID, c.UserFirstName, c.userEmail, f.statusID, f.statusName FROM Tickets a LEFT OUTER JOIN Type b ON b.typeID = a.typeID LEFT OUTER JOIN Users c ON c.userID = a.userID LEFT OUTER JOIN Status f ON f.statusID = a.statusID WHERE a.statusID <> 40 AND c.userEmail = ' + @userEmail + 'ORDER BY ' + @SortOrder)
Any ideas on what syntax I should be using? Thanks!
Is anybody here knowing how to create a dynamic query based on a multi-value parameter?
e.g. there is a multi-value report parameter called names. For a static query, the where clause of a select statement likes the following
select * from students where name=@names
For the dynamic one, I tried something like the below, but it did not work.
="select * from students where name=(" & Join(Parameters!names ,',') & ")"
Any suggestion would be great appreciated.
Thanks,
KY
I am trying to use one sql store procedure but don't know how to pass the parameters. I am posting here my store procedure:-
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
[Code] ....
My table looks like below:
emp_id, time_code, date, time_charged
RB, VAC, 20130222, 8
RB, HOL, 20131128, 8
RB, VAC, 20130311, 8
My output should be:
emp id VAC HOL
RB 16 8
The dynamic SQL statements with output parameters, unfortunately, in the documentation are not described.
À) Simple example of the dynamic SQL statement with output parameters
-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'.
declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000)
-- the third parameter passed in the dynamic statement as by output, returns the length of
-- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle
declare @nHypotenuse float
select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))',
@ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out'
-- we call the dynamic statement in such a way
exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse out
-- or in such a way
exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out
select @nHypotenuse -- Displays 5.0
B) Example of usage of the dynamic statement with output parameter and the function GETALLWORDS.
The following stored procedure get all words from a field of the type text or ntext, the word length should not exceed 4000 characters.
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT
@TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname, @cDelimiters nvarchar(256) = NULL
AS
-- this Stored procedure inserts the words from a text field into the table.
-- WORDNUM int – Sequence number of a word
-- WORD nvarchar(4000) – the word
-- STARTOFWORD int – position in the text field, with which the word starts
-- LENGTHOFWORD smallint – length of the word
-- Parameters
-- @TableName name of the table with the text or ntext field
-- @FieldIdName name of Id field
-- @FieldIdValue value of Id field
-- @FieldTextName name of field text or ntext
-- @cDelimiters Specifies one or more optional characters used to separate words in the text field
begin
set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit, @RetTable bit,
@cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000), @ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint
select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))), @FieldIdName = lower(ltrim(rtrim(@FieldIdName))), @FieldTextName = lower(ltrim(rtrim(@FieldTextName))),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- If the temporary table is not created in the calling procedure, we create the temporary table
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null
begin
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
select @RetTable = 1
end
-- we use the dynamic SQL statement to receive the exact name of text field
-- as we can write names of fields by a call of the given stored procedure in the arbitrary register
-- in the string of parameters definition @ParmDefinition we use a keyword output
-- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- Also we use a keyword output for definite before parameter
select @SQLString = 'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''''
select @ParmDefinition = '@FieldTextName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- we use the dynamic SQL statement to receive the exact name of Id field
select @SQLString = 'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+''''
select @ParmDefinition = '@FieldIdName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
-- we use the dynamic SQL statement to receive the type of field (text or ntext)
select @SQLString = 'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')'
select @ParmDefinition = '@TypeField varchar(13) output'
exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
-- we use the dynamic SQL statement to receive a length of the text field
select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@nEndString int output'
exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle
while 1 > 0
begin
-- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field
select @SQLString = 'select @cString = substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' +
cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@cString nvarchar(4000) output'
exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
select @nBegSubString1 = 1, @nEndSubString1 = @nEndSubString - @nBegSubString +1
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip the character not in word, if any
select @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip the character in word, if any
select @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString
begin
select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc
select @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1)
-- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000)
-- we add outcome result in the temporary table
insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD)
select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1, @nEndSubString = @nEndSubString + 4000
end
else
select @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
if @flag = 1
break
if @nEndString <= @nEndSubString
select @flag = 1, @nEndSubString = @nEndString
end
-- If in a calling procedure the table was not created, we show the result
if @RetTable = 1
select * from #GETALLWORDSFROMTEXT
end
GO
Example of the call Stored procedure SP_GETALLWORDSFROMTEXT
declare @cDelimiters nvarchar(256)
select @cDelimiters = '"-,.:!?«»()'+SPACE(1)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(12)
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
drop table #GETALLWORDSFROMTEXT
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name', 'Your Id field name', Value of Id field, ' text or ntext field name', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
select * from #GETALLWORDSFROMTEXT
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
-- GETALLWORDS(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value table
-- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- Example
-- declare @cString nvarchar(4000)
-- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
-- select * from dbo.GETALLWORDS(@cString, default)
-- select * from dbo.GETALLWORDS(@cString, ' ,.')
-- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions
CREATE function GETALLWORDS (@cString nvarchar(4000), @cDelimiters nvarchar(256))
returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)
begin
declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 1, @BegOfWord = 1, @flag = 0, @cString = isnull(@cString, ''),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while 1 > 0
begin
if @k - @BegOfWord > 0
begin
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word
select @wordcount = @wordcount + 1, @BegOfWord = @k
end
if @flag = 1
break
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndString > @k -- skip break characters, if any
select @k = @k + 1, @BegOfWord = @BegOfWord + 1
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndString > @k -- skip the character in the word
select @k = @k + 1
if @k >= @nEndString
select @flag = 1
end
return
end
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Hi All,
I Hope someone can help me out with this problem.. its pretty NB.
I have a pair of multivalued parameters. So, parent and child... the child needs to get its selection based on the selection made by the user on the parent parameter ( the child parameter is hidden).
Fictional values:
Parent Parameter: SelectAll, ALL , 1 , 2
Child Parameter: Home, Work, Play
If user selects "ALL" from the parent parameter I need Home and Play selected.
My idea was this:
=iif(Parameters!Parent.Value(1) = "All","Home,Play") <-- in Available values.
However, the dataset that reads this value does not like the coma delimited string.
This dataset is a cube created dataset, reading the child parameter.
Any help is greatly appreciated.
Kind Regards,
Neil
Hi all,
Is it possible to create a report with dynamic parameters or parameters dependant on other ones? I have the following scenario -
Parameter 1 - 'Select an Application' - List of applications that are available
Parameter 2 - 'Select a module' - This is a list of modules that needs to depend on Parameter 1. As a user selects different applications, the appropriate module list will be loaded.
Is this possible to do in SSRS?
Thanks,
Brian
I have only one table (Products) with 3 columns, PKID(int), CompanyID(int) and Product Name(nvarchar). The products table fills a CheckBoxList Control; the values of the ListItem's are the PKID of the Products Table.
When a user ticks the check boxes to search for products, I generate a comma deliminated string of ONLY CHECKED products. For example I now have a string "3,5,4,6" which represent the Product PKID's. We can assume the values are not in order.
My question is: Without knowing how many values are parsed to the stored procedure. How can I query the Products table for the CompanyID's?
I've been banging my head for days thinking how to structure such a DB, Stored Procedures and HTML Controls. Am I going about this the wrong way, some advice would be really appriciated.
Kind Regards.
Hello,
I have a report that uses a begin date and an end date as paremeters along with a user's email adddress to generate thier schedule for the coming week. I would like to schedule this report to run at the beginning of the week and email to each user based on thier email address. So the parameters will be system.date +1 and system.date+6 and user email. I have the users email addresses in a table so the 3rd parameter could be a "for each email in table users...". Is this posssible? Should it be in Report services or a scheduled Stored Procedure? Any help with syntax will be greatly appreciated!
Thanks,
Bob Myles
I need to pass the out put from a stored procedure as an input parameter to another stored procedure. I created a data set for each stored procedure. Fron the second data set parameter tab, I added a parameter to refer to the field from the first data set.
I get the error Fiels can not be used in query parameter expressions.
I tried two simple queries instead of stored procedures with first query feeding the second query. I get the same error. Is there any other other way to accomplish this?
Thank You.
Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?
Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"
2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
-- Al
Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?
Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"
2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
-- Al
Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?
Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"
2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
-- Al
I found the following paragraph while searching on here:
************************************************************************
You could easily set up a parameter for each column and then display that column conditionally based on the parameter.
For instance, if you have a column that displays First Name, you could have a parameter called DisplayFirstName. Then in design view you'd select the whole FirstName column and in the Visibility-Hidden property set it to :
=iif( Parameters!DisplayFirstName.Value = false, true, false )
This could easily become a big, unwieldy report, though, if you have a great number of dynamic columns. Also, The width of the report is set at design time, and it includes the width of all your columns, not just the visible ones. This could cause you some pagination problems.
************************************************************************
That is exactly my problem. I have lots of dynamic columns which causes the width of the report to be wide. Thus even though at run time the report only shows columns within a page, the report itself consists of a lot of white spaces after the selected columns. Does anyone know a solution to this? If not, I guess creating the rdl with code manually is the only way? Please advise. Thanks in advance.
- Will
Hi,
I created one report with 5 dynamic drop down parameters. The first parameter is passed from URL and the other's parameter drop downs should get populated based on the first one. These 4 parameters default to some value which I defined.
Everything is working fine in the Report Manager but when the report is integrated in a web page I am getting some problems.
After the drop-downs are populated and the user wants to filter down by using different values in the drop down, causes the report to generate automatically and all the dropdowns are reset to default.
Have you had this problem before? Anyone can advise me how to solve this issue?
Thank you in advance!
/daniel
I am trying to use SSRS to allow users to select any one or several columns from a set of cascading parameters which will then do a "data dump" of the contents of the selected columns for "export".I know how to do cascading parameters, but I am having problems coming up with a way of using the parameters to dynamically build a matrix which has as its columns the list selected in the parameters. I need the user to choose WHICH columns will be displayed.  There could be 1, 2, 3, up to 50 columns.
View 2 Replies View Related
I have a report with a multi-value parameter that gets its values (value and label fields) from a dataset that is built at runtime (from vbscript code that returns a string).
When I press 'preview' all works fine.
When I add another multi-value parameter that (like the first parameter) gets its value from a dataset that is built at runtime (from vbscript code that returns a string) and press 'preview', I get a strange behavior:
The second parameter is disabled until I choose an option for the first parameter.
I do not pass value from the first parameter to the second and the two variables have 'Null' in the default values and 'from query' in the available values.
Why does the second parameter is disabled and how can I avoid it?
Thanks in advance.
How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you
View 7 Replies View RelatedI would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,
I am having 3 tables, table1 table2 table3
User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).
I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,
TableA
CountryId CountryCode CountryName
1 IND INDIA
Update TableA
Set CountryCode = 'INDI', CountryName = 'INDIA'
Where CountryId = 1
On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone.
CREATE TABLE [dbo].tCountry(
CountryId bigint IDENTITY(1,1) NOT NULL Primary Key,
CountryName [varchar](50) NULL,
CountryCode [varchar](50) NULL,
CreateBy bigint
)
EXEC sys.sp_cdc_enable_db
[code]....
--See here, i want to display CountryName as NULL, because i didnt do any changes in this column
--Why am i asking this bcoz, Frm my front end application, i will send the complete list of columns and the values to the update query.
Select * from cdc.dbo_tCountry_CT Where __$operation in (3,4)
Can we pass dynamic values while pivoting?
Here is example
Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'
[Code] ......
I am using Ola's solution for db backups by the way and its main command is something like this:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBgrowth -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'MyDatabase1, MyDatabase2', @Directory = N'serverpath', @BackupType = 'FULL', @BackupSoftware = 'SQLBACKUP', @Compress = 'Y', @CompressionLevel = 2, @NumberOfFiles = 5, @Verify = 'N', @CleanupTime = 504, @CheckSum = 'N', @LogToTable = 'Y'" -b
where MyDatabase1 and MyDatabase2 are clients abover 100GB.
Now, I would like to make this dynamic, no user intervention. I already have a table where I'm keeping current and historical data of db sizes.
How can I make this job dynamic, so when a new database passes the 100GB threshold, their names will be part of @Databases parameter? In other words, added automatically, based on my query.
I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.
Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.
I have created two tables to illustrate the base data and the required output.
How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.
Please find code below. It should be quite self explanatory as to what I am trying to do.
IMPORTANT:
1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.
create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)
[Code] .....
I am new to SSIS and i got 1 assignment.
Requirement:
In my destination table i am having some 30 columns and the CSV files what i get may have 10 columns or 20. How do I map columns between source and destination dynamically?