I have a problem with SP when passing in Parameters. Basically something like this:
-- Pass 1, 2 or 3 as parameter
EXEC SP_mySP 1
-- The SP will do the following SQL Statement
SELECT * FROM myTable
WHERE
(
(If @Parameter1 = 1 then myColumn = 'A' or myColumn = 'B')
(If @Parameter1 = 2 then myColumn = 'C')
(If @Parameter1 = 3 then myColumn is not null)
)
How to make the above condition?
I know that you can retrieve whether a parameter is for output buy wayof the "isoutparam" field, but is there anything that tells you whethera parameter is input/output?thanks
View 3 Replies View RelatedHello,
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
5. Any other possible approaches ??
Looking forward to your responses,
Thanks much,
bmgun.
I am currently trying to find a way in which I can determine if a column in a Select clause is Japanese. The column currently supports English and Japanese Kanjis and other kanas. Is there a way to determine if this column is not English or if it is Japanese without physically looking at it.?
Thanks .... Chris
I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.
Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
View 11 Replies View RelatedHi All
I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.
Is there any way I can remove that option from the list?
Thanks
Hi everyone,
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original
- sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS
SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName
FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID
INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID
INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID
INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID
Where @status = A3.tStatus ...
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @date smalldatetime )AS SELECT A1.uLastName, A1.uFirstName, A2.mName, A3.tStatus, A3.tScore, A3.tStartDate, A3.tCompleteDate, A4.cName FROM VSALCP.dbo.[User] AS A1 INNER JOIN FSDLMS.dbo.Student AS A5 ON A1.uID = A5.stID INNER JOIN FSDLMS.dbo.Transcript AS A3 ON A3.t_FK_stID = A5.stID INNER JOIN VSALCP.dbo.Member AS A2 ON A2.mID = A1.u_FK_mID INNER JOIN FSDLMS.dbo.Course AS A4 ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...
"If status = ALL select * status"
Thanks for taking a look!
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO
Anyone have any ideas?
Thanks,
Deb
When passing the prameter values to a linked report, I want to set one of the parameter at the linked report to "Select All", this was added automatically to the parameter list when multivalue report parameter is used . How to set this "Select All" as a parameter or can it be done? Thanks
View 1 Replies View RelatedHi
I will like to bind the listbox control only with the rows where the colums text = categoryX
Where do I declare the variable @Category ?
If Not Page.IsPostBack Then
Dim conPubs As SqlConnection
Dim cmdSelect As SqlCommand
Dim dtrAuthors As SqlDataReader
conPubs = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
conPubs.Open()
cmdSelect = New SqlCommand("Select * From Slideshows WHERE ([Slideshows].[Category] = @Category)", conPubs)
dtrAuthors = cmdSelect.ExecuteReader()
ListBox1.DataSource = dtrAuthors
ListBox1.DataBind()
dtrAuthors.Close()
conPubs.Close()
End If
Best Regards
I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource?
ex: SELECT field1 from tblTableA where idTableA IN ( @Param1)
Let's say I want to pass 1,2,3,4 as Param1 (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4))
How I am supposed tu use the .SelectParameters.Add() to pass a list of integers instead of a single value??
Thanks in advance.
Is it possible to use a WHERE-IN statement with a SqlDataSource control. For instance:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="SELECT [Id], [Name], [Phone] FROM [Table1] WHERE ([Id] IN @Id)">
<SelectParameters>
<asp:Parameter DefaultValue="( 1, 3, 5, 7, 11 )" Name="Id" Type="Int32" />
</SelectParameters></asp:SqlDataSource>
I'm hoping the gridview would then display a table with rows for records 1,3,5,7,11. Thanks for any help...
Hi,
I am trying to get the login name of a user, trim off some characters, which works fine, convert this to a string variable, which works fine and then use this variable in an sql select parameter.
I've tried countless things and am not sure why my variable @NewString is not working in the Select command. If I add a default value I know is present, it works, but it can't seem to pick up the variable value of NewString. It prints out as expected in the response.write statement, but I really need it to connect to the corresponding value in the database.
Any ideas are greatly appreciated-code below.
Thanks,
Liz
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<SCRIPT LANGUAGE="vb" runat="server">
Public Function LoggedOnUser() As StringReturn (Request.ServerVariables("LOGON_USER"))
End Function
</SCRIPT>
<%Dim MyString As String = LoggedOnUser()
Dim MyChar As Char() = {"O"c, "N"c, "E"c, ""c}Dim NewString As String = MyString.TrimStart(MyChar)Response.Write("Hello ")
Response.Write(NewString)
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pboConnectionString %>"
SelectCommand="SELECT [LastName], [FirstName], [logon] FROM [Phonebook] WHERE ([logon] = @NewString)">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="NewString" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="logon" HeaderText="logon" SortExpression="logon" />
</Columns></asp:GridView>
</asp:Content>
Please can someone provide an overview of how to select all records in the parameter list. I am trying to get an understanding of how this works.
Having a union join with all value is fine, to retrieve the value of 'all'. However, given the data-set there is no 'all' value. From reading the ms course notes, the query syntax is: select from where = (field = @x) or @x = 'all'. How does this syntax work? Does x = all, so all= all = true then returns all records?
Regards,
Nijojo
Is it possible and how do I do use a select like query with an adocommand's parameter object?For Example:Instead of this:command.CommandText = "SELECT * FROM Table WHERE field LIKE'%searchString%'This:command.CommandText = "SELECT * FROM Table WHERE field LIKE '%?%'command.Parameters.Append(command.CreateParameter ....Thanks
View 3 Replies View RelatedWhich is more efficientWhere NonindexedColumn=x and IndexedColumn=yorWhere IndexedColumn=y and NonindexedColumn=xor does matter? Will the optimiser work it out?(I'm building the SQL string on the fly in the fron-end)
View 1 Replies View RelatedHy , I'he made a stored procedure with 1 parameter @cTableName and I want to return data from that table: select * from @cTableName , that is all. But it does not work. When I execute the stored procedurere I want to send the name of the table : execute MySP "MyTable". Is it possible? Thank you!
View 6 Replies View RelatedI set parameter as Multipul selection.
how to set it as "Select all" by default?
Thank you!
consider the following query
DECLARE @InputXML XML
SET @InputXML = '<Parameters><ID>123</ID><FileName><b>Hello
world</b></FileName><Count>3</Count></Parameters>'
SELECT
AT.value('FileName[1]', 'VARCHAR(50)'),
AT.value('Count[1]', 'INT')
FROM @InputXML.nodes('/Parameters') X(AT)
The file name column returns only "Hello World", how do I make it
return the FULL "<b>Hello World</b>" (with tags)
Thank you very much
I'm not sure if parameters is the correct solution, but it's all I can think of.
I'm trying to use a "template" report with a graphic logo and some other standard layout to provide all my subreports with the same look. On the "default" subreport, I intend to have links to the other subreports, but I can't find a way to dynamically change the subreport shown by the Subreport item.
Friends,
I have a report which has a Multi Value Parameter. I want the default to be selected as 'Select All'. Can anyone tell how to get this?
Thanks,
S Suresh
I have several databases on the same server that have the same table structures within them. Call them
Server.A
Server.B
Server.C
...etc.
I have designed a report that works in one database, say "A". I would like to add a parameter box to the report giving the user the ability to select which database to run the report in. I'm not sure if this is possible.
There may be more complex ways to handle this, say, with dynamic SQL or by building a large UNION of select statements across the various databases. But I figured I might ask for a simple solution first. :-)
I searched this forum however I could not find an answer to my question. I'm sure it has been asked before but...
In my parameter if the user does not select anything, null, then it returns all the records.
Second question if he selects "All" then it returns all records.
If this is 1st grade stuff please forgive me. I'm pretty new, learning as the needs arise.
Thanks
CardGunner
I have a select statement that I am using and wanted to know if there is a way to remove a parameter on the fly. What I want to do is remove the @status if the text = "". so it will only search by the date and endate is that possible to do. Here is my code.
Why does this not work? <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:Parameter Name="@employeeID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>In code behind: protected void OnSourceSelecting(object sender, SqlDataSourceCommandEventArgs args) { // THIS LINE THROWS EXCEPTION: An SqlParameter with ParameterName '@campaignID' is not contained by this SqlParameterCollection. args.Command.Parameters["@employeeID"].Value = 3; // In future, will use dynamic value. } If, instead I do this: <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:QueryStringParameter Name="@campaignID" QueryStringField="id" /> </SelectParameters> </asp:SqlDataSource>(Of course I call page with EmployeePage.aspx?id=123)I get the exception: Must declare the scalar variable "@employeeID".
View 5 Replies View RelatedI Have a SQLDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:SqlServer %>"ProviderName="<%$ ConnectionStrings:SqlServer.ProviderName %>"SelectCommandType="StoredProcedure"SelectCommand="pe_getAppraisals" ConflictDetection="CompareAllValues"><SelectParameters><asp:ControlParameter ControlID="ddlPType" PropertyName="Value" Direction="Input" Name="PType" ConvertEmptyStringToNull="true" /><asp:ControlParameter ControlID="ddlClient" PropertyName="Value" Direction="Input" Name="Client" ConvertEmptyStringToNull="true" /><asp:Parameter Direction="Input" Name="PageSize" DefaultValue="20" Type="Int32" /></SelectParameters></asp:SqlDataSource>
When I load the page no results are returned. The only way I get results is to select an item from both drop down lists... My stored procedure works if I pass NULL or '' (an empty string) for PType and.or Client. Any Ideas on how to fix this or work around it? Is there any way for me to see what is being passed to the database for these values before I select an item from the dropdown lists?
Thank YouJason
Hi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name
but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.
I tried
select @num = max(field1) from object_id(@name)
and the error was the same
is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg
If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?
View 1 Replies View RelatedI have a report with a dataset/parameter to select the salesperson.
SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID
FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN
[DatabaseName$Sales Shipment Header] ON
[DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code]
UNION ALL
SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID
ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name
In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".
Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
Hi All
Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.
My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..
I'm using SSRS with SSAS cube all in BI all 2005
Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.
Thanks in advance
Gerhard Davids
I have a series of "Payroll Periods", called S1 thru S26 for the year. I want to create a parameter call "Pay Period" and have the user select a single pay period, i.e. S13. Elsewhere I know the beginning and ending date for each pay period, but it is in another dataset. So if the user selects S13 and I know the 13th pay period is April 1st thru April 15th, how do I use those dates as my beginning and end dates for my SQL query statement to return only the data from the selected pay period. The data itself has dates and not pay period references.
View 1 Replies View RelatedIs it possible to pass values from UI to a multi-value parameter in a report and from this report, select values from this multi-value parameter to finally display data?
Thanks!