How To Change The Value Of The Parameter When Call
Sep 24, 2007
Hi
I have below sql procedure
CREATE PROCEDURE Qty
@make as nvarchar(32)
AS
select department as category from tblproducts where department = @make
GO
Parameter (@make) will get the value from asp.net page
My Question: Is any possibilities to change the value of parameter when the procedure trigger. Examlpe like below
If @make = 'Sealed Lead Acid' then I need to pass parameter to the database as 'SLA'
elseIf @make = 'Photographic' then 'Multimedia'
elseif @make = 'cctv and accessories' then 'security'
and so on. I only have six categories. Please help
Advance Thanks
View 3 Replies
ADVERTISEMENT
Aug 9, 2006
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.
Help is much appreciated.
View 4 Replies
View Related
Jul 18, 2014
I have created a DTS package which stores file data into Database table.
I want to pass file name dynamically to stored procedure from which DTS retrieve data and store it in table.
View 3 Replies
View Related
Jul 23, 2005
Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.
View 4 Replies
View Related
Apr 2, 2007
We have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
Thank you in advance for your help.
View 2 Replies
View Related
Apr 2, 2008
I need to be able to call a SSIS package with a specific parameter for one of the variables. Basically the package needs to be triggered from an application (.NET, C#) with a specific value.
The package is stored in the SQL Server (in Stored packages).
I have seen the article on dtexec and the SET flag, however we do not want to turn on xp_cmdshell on the SQL server due to the security implications, and that seems to be the only way to call dtexec
I have seen another article that says you can have a job invoke a package then have a specific user granted permission to the job, but that doesn't address how to set the parameter/variable for the package.
There are actually some other steps that technically need to run using the same parameter, so ideally if I could have a stored procedure call the SSIS package, then the subsequent toher stored procedures using the sent in parameter that would be great. I just can't figure out how to do this securely (as xp_cmdshell is not considered secure).
Thanks for any comments.
View 12 Replies
View Related
Aug 3, 2007
I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
View 5 Replies
View Related
Sep 15, 2014
In t-sql 2012, the followinng sql works fine when I declare @reportID.
IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION
[code]...
However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:
FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)
GO
how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?
View 2 Replies
View Related
Oct 26, 2007
Hi,
I would like to call the report from an external source:
The report requires a paramater.
I tried the following:
<a href='http://Report.aspx?ItemTakeOn%2fItemTakeOn&ProcessNo=278'> Report</a>"
The link works & i added the parameter in the link (ProcessNo=278)
it opens the report but i still have to manually insert the Parameter.
Also tried setting the parameter as hidden.
How do i bypass this?
Anyone please Assist!
Regards
View 1 Replies
View Related
May 23, 2007
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
CallWebService.Credentials = System.Net.CredentialCache.DefaultCredentials
Call CallWebService.IntelisisService(ArchivoOriginal)
My class rperez.IntelisisServiceEndPoint is referenced (hardcoded) in the dll project I need it dynamic
Do I need to change my code to PROXY object ?
View 1 Replies
View Related
Jun 15, 2015
I am working on SSRS. Based on parameter value I needs to call the related view. Below are my current expression query. but it has some error.
=IIF(Parameters!RacServiceOfferingsOnly.Value=0, SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_N WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate) AND
(:TransferType IS NULL OR :TransferType = "Direction"),SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_Y WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate))
View 2 Replies
View Related
Sep 7, 2015
How to call Boolean/Text parameter in SSRS Query.
Parameter (Cust )Properties:
My Query:
SELECT  dbo.incident.incident_ref, Customer.cust_n,incident.date_logged FROM
incident INNER JOINÂ Customer ON incident.incident_id = Customer.cust_id
WHERE Â incident.date_logged BETWEEN @date_from AND DATEADD(day, 1, @date_to)
I need to use a condition here to display customer based on my selection in dropdown list (Include ABC )
View 5 Replies
View Related
Jun 15, 2007
I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:
PROCEDURE Interface_Begin
(p_from_dttm OUT varchar2,
p_error_code OUT number,
p_error_text OUT varchar2,
p_proc_name OUT varchar2);
View 10 Replies
View Related
Mar 27, 2008
I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
View 6 Replies
View Related
Jun 17, 2015
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
Â
CREATE PROCEDURE [App].[ServiceUser_Save]
@userID int-- Param #0
,@serviceuserID int-- Param #1
,@version int-- Param #2 etc...
And then in a separate stored procedure we have the following
CREATE PROCEDURE [Admin].[CreateMiscellaneousData]
@customerIDint,
@serviceIDint,
@fullURL nvarchar(255),
@apiUserPwd nvarchar(255)
AS
BEGIN
...
EXEC@return_value = [App].[ServiceUser_Save]
@userID = 1,
@serviceuserID = 0, etc...
Note there is no value passed for the @Version parameter.
What I want is the build to fail because of the missing parameter on the call to ServiceUser_Save in the Create_MiscellaneousData stored procedure.
How can I achieve this?
View 4 Replies
View Related
May 19, 2008
hi,
I have a dropdownlist parameter like Today, 1 week ago, 2 weeks ago
and I have 2 datetime parameters.
What I want is when I select 1 week ago in the dropdownlist parameter
I want other date parameters to be today-7 and today respectively.
Is that possible?
Thanks
View 4 Replies
View Related
May 22, 2008
Hello.
I have a gridview that is connected to a sql datat srource witch gets a few columns to populate the GV with. I also have a drop down list where i enable the user to fill the gridview with the values that only match a specific category. What iv done is that i have a selected index changed event on my drop down list that does the following.string kategori = DropDownListKategori.SelectedValue.ToString();
Label1.Text = kategori;
SqlDataSource1.SelectCommand = "SELECT [ARTNR], [ARTTYP], [AKTIVITET], [DATUM], [KUND], [PLATS], [KOMMENTAR] FROM [ARTIKEL] WHERE [ARTTYP] = @ARTTYP";
SqlDataSource1.SelectParameters.Add(@"ARTTYP", kategori);
SqlDataSource1.DataBind();
Now when i launch the webpage and select a category (kategori) the gridview chanes and displays only the values that match that category but when i slect a new category Visual Studio throes me the following.
The variable name '@ARTTYP' has already been declared. Variable names must be unique within a query batch or stored procedure.
I thought that the parameter would get a new value but aparently when its set i cant declare a new value inside. What id like to know is how i can modify the above code so i can pass a new value to my select as soon as the drop down changes.
View 3 Replies
View Related
Aug 19, 2006
Hi all,
I read the Tang and Lennan's book 'SQL Server 2005 Data Mining'. It frequently says
about changing the value of the algorithm's parameter. I tried changing values for
MINIMUM_PROBABILITY in one occasion. However, the dialog box refuses to
change the value; for any of 'Value', 'Default', and 'Range'. Similar thing happens
when I tried to change the value.
What I can do is delete the parameter, then add it again. Even so, I can only supply
the value, not the default value and range (of values).
What happen? Again, is this because I'm using evaluation copy?
Thank you,
Bernaridho
View 3 Replies
View Related
May 27, 2014
Our client is asking to display the 'parameter panel in right side of the report' insted of top.
how can I do this?
View 2 Replies
View Related
May 31, 2007
One of our Sales Reps is having issues with a report I've made and as far as I know he's the only one (Out of about 50) who has had this issue. He's running IE 7. What happens is he'll enter in the parameters and run the report and it works fine. But when he goes to change the dates he entered as parameters and then clicks "View Report" again to run the report again, the browser just redisplays the same results and doesn't even generate a new report. It appears as if the browser has cached his previously ran report and then just redisplays it instead of going back to the server and re-generating the report. Has anyone seen this before or have any ideas? I appreciate it.
JD
View 1 Replies
View Related
May 13, 2007
I have created an ASP.NET website with a ReportViewer on. The report renders fine, but when I change one of the parameters (manually), the report seems to invalidate itself and a blank while area is drawn. I would like the same behaviour as in the WinForms variant, where rhe rendered report remains the same until the 'View Report' button is pressed.
Is this possible?
Regards Andreas
View 1 Replies
View Related
Jan 24, 2007
Hi all,
I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).
...
From date To Date
...
From age To Age
...
From Salary To Salary
@salary is the last one.
when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)
Range operator ( : ) operands have different levels; they must be the same.
BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.
Any idea and suggestion will be appreciated!
Jone
View 1 Replies
View Related
Jun 25, 2007
Hi All,
I'm using report with a date parameter (user enters a date) and all transactions before that date are displayed. I don't know how to set parameter date format on the displayed report to dd/mm/yyyy.
If it's not a parameter I usually use sql " convert(varchar(10),datefield,103)" but don't know how to use this with parameter.
Thanks
Sonny
View 3 Replies
View Related
Sep 8, 2007
My value is approx 50 characters wide, the parameter drop down is only displaying about 10 characters, although I have plenty of room on the screen.
Also, in a multi-parameter environment, are there any controls on where the parameters are being displayed?
thanks
View 2 Replies
View Related
Apr 7, 2008
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
View 12 Replies
View Related
May 2, 2008
Hi All,
I'm having trouoble with changing my report name when I swtich to different parameter values.
I use the following expression.
IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total"))
This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work.
Is there any solution for this?
Thanks
View 1 Replies
View Related
Jan 11, 2006
Hi
I have Try to Create Stored Procedure in C# with the following structure
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)
{
}
But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature
@ImageID uniqueidentifier
@ImageFileName nvarchar(4000)
@Image varbinary(8000)
But I don€™t want that Data types .. I want it to be in the following format
@ImageID uniqueidentifier
@ImageFileName nText
@Image Image
How Can I Control the data type for each parameter ??
Or
How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
Or
How Can I defined the new Data type ??
Or
What's the solution to this problem ??
Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP
ALTER PROCEDURE [dbo].[sp_AddImage]
@ImageID [uniqueidentifier],
@ImageFileName nText,
@Image Image
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
GO
And thanks with my best regarding
Fraas
View 7 Replies
View Related
Dec 18, 2007
I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school.
When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools.
I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.
View 4 Replies
View Related
Apr 22, 2015
In an existing SSRS 2012 report, I have a requirement for a user to be able to select by multiple school(s) and/or multiple grade(s). This is fine except certain schools like elementary have grade levels of KG to 06, Middle school has grades of 06 to 09 and high school has grades of 10 to 12. Thus for example, if a user has initially selected grades 11 and 12 and then they select an elementary school that has grades KG through 06, the 'grade level' selection would need to change.In other words, I am thinking of initially having the available and default values for the parameter called 'Grade' set to KG through 12. However if a school is selected that only has specific grade levels like elementary school, how can I override the original grade level and only allow the user to select grades that the particular school contains?
View 3 Replies
View Related
Jun 24, 2015
I want to Change parameter position in SSRS report.I need Start Date and End Date Parameter at same column so client can easily select Start Date and End Date.I have tried a lot but not succeed.
How Can I change the position of Start Date and End Date.I can't change order of other three parameter Select Institutions, Select Author and Course Pack Names parameter because they are internally related to each other(forward dependencies are there).
View 2 Replies
View Related
Aug 26, 2015
I have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.
But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.
View 2 Replies
View Related
Mar 19, 2008
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
View 7 Replies
View Related
Jul 20, 2005
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies
View Related
