Can I pass a sort expression into a store procedure as a parameter? I
want to do sth like the following but do not know if it's possible.
Maybe I need some tricks to work around?
CREATE Procedure SelectAllRoles
(
@SortExpression varchar(20)
)
AS
IF @SortExpression <> ''
THEN
SELECT Id, Name, Name2, LastUpdated
FROM [Role]
ORDER BY @SortExpression
I have a SQL Server 2005 Stored Procedure that takes a parameter to sort on: ----------------------------------------------------------------- ALTER PROCEDURE [dbo].[GetAllApplications_Sorted]
@sortType varchar(25)
AS SET NOCOUNT ON; SELECT aev.ApplicationID, aev.ApplicationName, aev.EventMessage, aev.MachineID, aev.MachineName, aev.EventLevel, ellu.EventImg, ellu.EventLevelID, dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID) AS maxEventLevel FROM dbo.ApplicationEventView aev, dbo.EventLevelLookUp ellu WHERE ellu.EventLevelID=dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID) AND aev.EventLevel=ellu.EventLevel ORDER BY ellu.EventLevelID + ' ' + @sortType ----------------------------------------------------------------- Problem is I keep getting this error: Conversion failed when converting the varchar value 'desc' to data type int. And I can't figure out why...
hi, i searched a lot to find how to pass an orderBy parameter finally i used a case block in my code and it works now how can i add a second parameter for ascending and descending order(@sortAscOrDesc) when i use it after the end of case statement i get error here is my sp:CREATE PROCEDURE [userPhotos] @userID int,@orderBy varchar(100) ASSELECT ID,UserID,Photo,ALbumID,Title,views,date_added from userAlbumPic where userID=@userID and albumID=0 order by case @orderBy when 'date_added' then date_added when 'views' then [views] else date_added end GO
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
I have this select statement: SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR [title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch + "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%" + strSearch + "%')
This statement works fine but when I turned it into a store procedure, it returns nothing. I created the store procedure as follows: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE sqlSearch -- Add the parameters for the stored procedure here @strSearch varchar(250) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR [title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" + @strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR [content_nws] LIKE '%" + @strSearch + "%') END GO
I have searched this forum for how to call a store procedure with parameter and so far no result has answered my question yet. I have the following store procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
-- Add the parameters for the stored procedure here
@strSearch varchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR [title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" + @strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR [content_nws] LIKE '%" + @strSearch + "%')
END
GO
I got this far in my code on how to call the parameter store procedure. string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar); I don't know what do next.
Hi, I am using a ListBox where a user can choose multiple lines. The index of the selected items are then used in a stored procedure.
I wan´t to use the ID´s in this statement: SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)
But how can I do this? If I pass them as a string, then I can´t use them as above. Can I separate the string '1,2,4,9' so I can use them in the statement above? Or can I send the values as a array to the stored procedure?
Hi Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on. It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'. here is my code: ' build calendar for appointment sessions Dim Today As Date = Date.Now Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString Dim Conn As New SqlConnection(ConnStr) Conn.Open()
Dim cmd As New SqlCommand("GetAppointmentSessions", Conn) cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime) Dim adapter As New SqlDataAdapter(cmd)
Dim dt As New DataTable
adapter.Fill(dt)
Dim row As DataRow Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]
@InputDate Datetime AS
SELECT TOP (5) uidAppointmentSession, dtmDate, (SELECT strRoomName FROM tblRooms WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room, (SELECT strName FROM tblHMResources WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician, dtmStartBusinessHours, dtmEndBusinessHours FROM tblAppointmentSessions
i am using asp.net 2005 with sql server 2005. in my database table contains Table Name : Page_Content
Page_Id 101 102
1 Abc Pqr
2 Lmn oiuALTER PROCEDURE [dbo].[SELECT_CONTENT] (@lang_code varchar(max)) AS begin declare @a as varchar(max)set @a = @lang_code
Select page_id,@a From page_content end Here in this above store procedure i want to pass 101 to @lang_code here is my output, but this is wrong output
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
Some one please tell me how do I write store procedure that receives/takes parameter values I want to write store procedure which takes ID as a parameter
some one tell me how do I write store procedure that takes parameter if possibel please show me example of it
Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.
using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.
cid        oid      items 1         1           10 1          2         12 1          3          3 1       4              4
so if we enter 1 as custid it got to give us 12 as the result..
I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).
I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .
Now i am using dataset with this store procedure  to fill my @USER_IDS  parameterÂ
Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error
and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..
Hi, I have a report that needs sorting on a column that is calculated. however, the calculation is so complex (iterative calculations) i've had to do that within my custom code. I am getting the results back well and good but i cannot perform a sort based on that. My problem is: how do i write a custom sort expression function that will direct the column to sort my table based on say the value(s) returned from that function. I want for example:
Code Snippet Public Function mySortExpression(byVal myIDorWhatever as Something) as CustomSortableExpression
' Perform some calculation ' Return some value(s)/array or anything that the column can appreciate and sort the table accordingly with End Function
then in say the Interactive Sort Sort Expression, i'll have the code:
I have a "Sales shipments" report with several colums. I want a parameter for the user so he choose how to sort. I have two colums in my report I want to sort on: ItemNumber and DeliveryDate.
I added a Report Parameter UserSort with two labels (and values): Item (ItemNumer) and Date (DeliveryDate).
Hi, I have a matrix report containing a parameter.The count of groups may change based on the parameter's value.Also these groups are connected to each other with toggle property. For example; --If the parameter's value is "year", the report have three (year, month, day) groups.Day is connected to month and month is connected to year with toggle property. --If the parameter's value is "month", the report have two (month, day) groups.Day is connected to month with toggle property. --If the parameter's value is "day", the report have one (day) group.There will be no toggle property.
The question is: I wrote expressions for this report and set the visibility properties of the groups, but when I select the "day" or "month" value for the parameter, no groups are shown.I think the reason is "being connected to each other with toggle property".
Now I can query dataset and bind to report parameter. But I have some problem about setting default value. I don't know, how to select max value from dataset by using expression.
Example, I query disticnt year from my table. The result has 2 years "2007" and "2008" select distinct year(mytime) as myyear from mytable.
I set available parameter by using this dataset. Then, I set default value for this parameter by the other dataset ...
select max (distinct year(mytime)) as maxyear from mytable.
I want to select max value by using expression, not select max by using dataset.
I am trying to set parameter in reporting services that lets the user select "All Hours" or "Cour Hours" of the day in a given date range.
Core hours would filter the transactions (and aggregations) that happened between 6am and 10pm on certain days and another range of hours on some days. The days that the core hours exist will be static. i.e. every day the core hours are 6am to 10pm but on Mondays the core hours are Midnight to 2am and then 6am to 11:59:59.
I am trying to figure out what that code would look like. Any help is appreciated.
ID      Name   Date 1           A        null 2          B       01/01/2012 3          C       01/02/2013
Also, I have a sort parameter @sort and values are (Name, ID, Date)
I want to apply page break whenever @sort=Name. There should be no page break when user selects @sort = ID or Date. Page break should happen only when @sort value = Name
it should be like this...
Page 1: ID      Name   Date 1          A        null Page 2: ID      Name   Date 2          B        01/01/2012 Page 3: ID      Name   Date 3          C       01/02/2013
@StartDate is a report parameter and CustomerNo is the field I want to render on the report,I want to combine the sql ,but it display the error message below when I preview the report.If I query in the design form and input the value of the parameter ,it run correctly . --------------------------------------------- Data sql: declare @sql nvarchar(4000) declare @sqlWhere nvarchar(4000) set @sql='select CustomerNo from table1 ' set @sqlWhere=''
if @StartDate<>'' begin set @sqlWhere =@sqlWhere + ' StartDate=''' +@StartDate + '''' end if ltrim(rtrim(@sqlWhere))<>'' begin set @sqlWhere =' where ' + @sqlWhere end set @sql=@sql+@sqlWhere
exec sp_executeSQL @sql ------------------------------------------ Error message: [rsFieldReference] The Value expression for the textbox €˜CustomerNo€™ refers to the field €˜CustomerNo€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.
My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.
The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.
How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?
I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.
I need to use a non-queried report parameter to filter a dataset for a report.
The dataset column I'm filtering is numeric. The dataset is not a sproc, it's a table in SQL Server that I am querying.
The Non-queried parameter values (Multi-value) are 1, 2, 3, 4, 5, >=6.
Selecting the >=6 throws the error: "Error converting data type nvarchar to numeric."
Which sucks.
Because...when I go straight into my dataset I can filter my numeric column with the exact same values (=1 or =3 or >=6) and everything works fine. The error is only raised when I use the @Parameter in the dataset.
I've tried eveything, researched everywhere online and I can't find any guidance anywhere.
hello guy...i have a problem with my coding....i have store procedure that already create in mssql server 2005...that store procedure have a parameter.... Store procedure code set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Users_login] @username varchar(50), @password varchar(50) AS BEGIN SET NOCOUNT ON; SELECT User_application_id, User_fname, User_lname, User_last_activity FROM Users U WHERE U.User_name = @username AND U.User_password=@password END this is my asp.net code Dim connLog As New SqlConnection Dim cmdlog As New SqlCommand Dim readerLog As SqlDataReader
connLog.ConnectionString = ConfigurationManager.ConnectionStrings("PVMCCon").ToString() With cmdlog .CommandType = CommandType.StoredProcedure .CommandText = "[Users_login]" .Connection = connLog .Parameters.Add("@username", SqlDbType.VarChar, 50, userTxt.Text.ToString()) .Parameters.Add("@password", SqlDbType.VarChar, 50, passTxt.Text.ToString()) End With Try connLog.Open() readerLog = cmdlog.ExecuteReader() If readerLog.HasRows Then While readerLog.Read usernameStatusLabel.Text = readerLog("User_fname").ToString() & " " & readerLog("User_lname").ToString() lastActivityLabel.Text = readerLog("User_last_activity").ToString() Session("AuthenticatedID") = readerLog("User_application_id").ToString() End While PanelLoginSuccess.Visible = True PanelLoginView.Visible = False Else SignInPopupExtender.Show() End If Catch ex As Exception loginStatusLabel.Text = ex.ToString Finally connLog.Close() End Try End Sub so the problem is...how can using the store procedure that already have a parameter and how i want to set the parameter value in my code?????
Hello, I have a Repeater control getting data from a SqlDataSource.The SqlDataSource uses a stored procedure which accepts a input parameter with the name "sort" (@sort). How do I use the FilterExpression with FilterParameter for defining the input value for the stored procedure ?The input value comes from an asp:QueryStringParameter.Or can the FilterExpression only be used with SQL Statements defined directly (as for defining the where clause) ? I guess the best way is to use the FilterExpression soo the data is cached in the Dataset which is needed/required when using FilterExpression.Alternativly I could use the SelectParameter instead FilterParameters, but I guess the data wont be cached then.Anyone who can help me and guide me in the right direction ? Best regardsMartin
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....
