RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It
Feb 19, 2007
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!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
ADVERTISEMENT
Jul 2, 2007
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
View 1 Replies
View Related
Sep 29, 2015
Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
View 1 Replies
View Related
May 4, 2015
I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. Â I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :Â
Execute statement is like
Exec dbo.storedProc1Â select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24'Â .
Somehow passing the date as parameter is giving me empty result set.
View 2 Replies
View Related
Oct 12, 2005
How to pass a XML data parameter to an SQL 2005 Stored Procedure
I hope to insert a xml data into an typed xml column in SQL 2005.
1. I can run the Code 1 correctly.
2. I hope that I can pass a XML data parameter to an SQL 2005 Stored Procedure, So create the Code 2. but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: Only non-document nodes can be inserted. Found "xs:string ?".
3. I create the Code 3, but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: ',' or ')' expected
4. I create the Code 4, but I get the error below:XQuery: SQL type 'xml' is not supported in XQuery.
//--------------------------Code 1-------------------------------------create procedure Hellocw_InsertBookmark40@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert <x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 1-------------------------------------
//--------------------------Code 2-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 2-------------------------------------
//--------------------------Code 3-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert cast(sql:variable("@Insertxml") as xml)as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 3-------------------------------------
//--------------------------Code 4-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml xmlasupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId
//--------------------------Code 4-------------------------------------
View 2 Replies
View Related
May 8, 2007
Hi, I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in. Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue nor does EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L
View 1 Replies
View Related
Dec 19, 2007
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
View 5 Replies
View Related
Oct 9, 2006
Hi,
I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error,
€œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ
Through research I have no clue as what I need to do, or where to look.
The package runs without error when I execute the package itself.
Any help is greatly appreciated.
Thanks,
Lori
View 1 Replies
View Related
Feb 25, 2004
Hi,
I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.
The exact error message that occurs is:
ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput)
I need to know what to write instead of advarwchar?
Thanks in advance
View 1 Replies
View Related
Apr 18, 2007
Hello,
I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.
To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.
try{
pCmd.CreateInstance((__uuidof(Command)));
pCmd->ActiveConnection = m_pConnection;
pCmd->CommandType = adCmdStoredProc;
pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");
............................ code here ........................................
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));
VARIANT returnVal;
returnVal.vt = VT_I2;
returnVal.intVal = NULL;
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));
//Get Return value by executing the command
//The return value should be the DB unique ID.
pCmd->Execute(NULL, NULL, adCmdStoredProc);
int uniqueId = returnVal.intVal;
//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);
//GetFieldValue(0,pRst,uniqueId);
printf("The DB unique ID is: %i",uniqueId);
return uniqueId;
}
Cheers,
Seth
View 1 Replies
View Related
Jul 20, 2005
How can I make a stored procedure which has a output parameter withtext data type? My procedure is:CREATE PROCEDURE try@outPrm text OutputASselect @outPrm =(select field1 from databaseName Wherefield2='12345')GOHere field1 is text data type.Thanks
View 1 Replies
View Related
May 27, 2015
I want to create a CLR Stored Procedure with a Table User Defined Type like this short example in SQL:
CREATE TYPE [dbo].[TypeTable] AS TABLE
(
[Id] [integer] NOT NULL,
[Value] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
[Code] ....
I found some example in this link : CLR UDT
But I can't figure how to do the same with a User Defined Type Table.
View 5 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
Sep 4, 2007
Hi guys, is there any way to solve my problem as title ? Assuming my stored proc is written as below :
CREATE PROC TEST
@A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000'
AS
BEGIN
DECLARE @B nvarchar(MAX);
SET @B = 'SELECT * FROM C WHERE ID IN ( ' + @A + ')'
EXECUTE sp_executesql @B
END
GO
View 2 Replies
View Related
Oct 26, 2006
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve
the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?
Any workaround?
Here is the pseudo code:
create Procedure foo(@output varchar(max))
{
set @foo = 'string that has more than 4000 characters...';
return;
}
Java code:
CallableStatement cs = connection.prepareCall("{call foo ?}");
cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.
cs.execute();
String result = cs.getString(1); // The result is truncated to 4000 char.
-- Also tried
CLOB clob = cs.getClob(1);
long len = clob.length(); // The result is 4000.
Thanks,
Eric Wang
View 3 Replies
View Related
Apr 21, 2008
Is it possible to include a text/string as suffix(append to the end of textbox) to an report parameter prompt string ? Something like this
Enter Date: [ Textbox ] format:mm/dd/yyyy
Enter Age:[Textbox ] e.g 50
I need to include text like the one that I have highlighted in blue. Could someone help me out?
View 7 Replies
View Related
Nov 19, 2007
I have tested the following code, and it works for me:
DECLARE @XML XML
SET @XML = '
<DocumentElement>
<data>
<item code="ABCDEFG" quantity="1" sort="0" />
<item code="XCFVGBF" quantity="1" sort="0" />
<item code="ABCDEFG" quantity="10" sort="0" />
</data>
</DocumentElement>'
SELECT SUM(x.qty * ISNULL(p.price_mn,0))
FROM (SELECT [Code] = A.A.value('@code','varchar(20)'),
[Qty] = A.A.value('@quantity','INT')
FROM @xml.nodes('/DocumentElement/data/item') AS A(A)) X
LEFT JOIN productprice_t P ON p.code_id = x.code
The question is, how can I do this if the XML is in a different format & doesn't use any attributes?
So it looks like this (which I'm getting from an ADO.Net datatable using .WriteXML):
<DocumentElement>
<data>
<code>ABCDEFG</mercurycode>
<quantity>1</quantity>
<sort>0</sort>
</data>
<data>
<mercurycode>XCDFEG</mercurycode>
<quantity>2</quantity>
<sort>0</sort>
</data>
<data>
<code>ABCDEFG</mercurycode>
<quantity>10</quantity>
<sort>0</sort>
</data>
</DocumentElement>
View 5 Replies
View Related
Mar 19, 2007
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.
When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects.
However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.
Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ?
I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.
Any help and/or suggestions are appreciated !!!
Thanks.
View 8 Replies
View Related
Nov 30, 2007
I have a requirement where, I need to get the value of parameter from a query string of url and use it in my report.
Ex:- http://www.mysite.com?name=bobby
From the above url, I have to take the value of name and be able to use it in my report query or assign to a parameter.
Please let me know, how can I acheive this.
View 1 Replies
View Related
Jan 28, 2008
I have a boolean parameter on my report that the user will enter. Currently, it shows as an option button with True/False beside each option as the words used. Is it possible to have a check box instead of an option button for the user instead?
Thanks in advance!
View 3 Replies
View Related
Jan 9, 2007
Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.
I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?
Thanks!
Terry B
View 1 Replies
View Related
Aug 10, 2015
I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
View 5 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Apr 22, 2015
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.Â
Is this a bug in Visual Studio? How do I get around this?Â
View 3 Replies
View Related
Oct 18, 2006
I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
View 4 Replies
View Related
Apr 9, 2008
I am using two drop downs, like so:
<asp:DropDownList ID="ChurchStateDrop" runat="server" DataSourceID="ChurchStateDropData" DataTextField="State" DataValueField="State" AutoPostBack="True" onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList>
<asp:DropDownList ID="ChurchCityDrop" runat="server" DataSourceID="ChurchCityDropData" DataTextField="City" DataValueField="City" AutoPostBack="True" onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All Cities" /></asp:DropDownList>
I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [State] FROM [ChurchView]" DataSourceMode="DataReader"></asp:SqlDataSource>
<asp:SqlDataSource ID="ChurchCityDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [ChurchView] WHERE ([State] = @State)" DataSourceMode="DataReader"> <SelectParameters> <asp:ControlParameter ControlID="ChurchStateDrop" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource>
Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).
View 3 Replies
View Related
Jul 23, 2005
I am new to sql and very familiar with access. I am using a verylarge database(130M records) in ms sql2000 and think I need to frontend it with access for reports and forms, etc..I have some questions:1) Is there a way in SQL to prompt a user for input at the running ofa query like the [what is your name] construct in an access query?2) I can't seem to create calculated fields in a view in sql. Inaccess I put "total:=hours*rate" and a new column would be createdwhich would contain the total.3) I normally would create a form in access from which the accessqueries would pull their variables and then insert those variables inthe queries or reports, etc. How would this be accomplished usingsql.4) Lastly and probably most basic. How does a "real" sql developercreate reports and forms to interact with the enduser? I am usingaccess as a front end, but am open to suggestions.Thanks,Brad
View 1 Replies
View Related
Jun 4, 2007
I have the follwoing stored procedure:ALTER procedure [dbo].[up_GetExecutionContext](@ExecutionGUID int = null) asbeginset nocount ondeclare@s varchar(500)declare @i intset @s = ''select @s = @s + EventType + ','-- Dynamically build the list ofeventsfrom(select distinct top 100 percent [event] as EventTypefrom dbo.PackageStepwhere (@ExecutionGUID is null or PackageStep.packagerunid =@ExecutionGUID)order by 1) as xset @i = len(@s)select case @iwhen 500 then left(@s, @i - 3) + '...'-- If string is too long thenterminate with '...'else left(@s, @i - 1) -- else just remove the final commaend as 'Context'set nocount offend --procedureGOWhen I run this and pass in a value of NULL, things work fine. When Ipass in an actual value (i.e. 15198), I get the following message:Invalid length parameter passed to the SUBSTRING function.There is no SUBSTRING being used anywhere in the query and thedatatypes look okay to me.Any suggestions would be greatly appreciated.Thanks!!
View 2 Replies
View Related
Jul 30, 2007
Hi all,
I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.
DECLARE @strSearchType varchar(2)
DECLARE @strSearchValue varchar(15)
DECLARE @SearchField varchar(15)
set @strSearchType = 'PN'
set @strSearchValue = '1234567'
IF @strSearchType = 'PN'
begin
set @SearchField = 'PART_NUMBER'
end
ELSE
begin
set @SearchField = 'INTERNAL_SKU'
end
SELECT
SKU as 'SKU',
PART_NUMBER as 'PartNumber',
DESCRIPTION as 'Description'
FROM SKUs
WHERE @SearchField = @strSearchValue
FOR XML PATH('SKU'), ROOT('Response')
Thanks,
ST
View 4 Replies
View Related
Nov 8, 2007
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters.
What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible?
As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment.
Seems like this should be possible... Any idea?
Cheers, Mike
PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
View 6 Replies
View Related
Apr 19, 2007
Hi there everybody,
I am writing a sales managing software using C# in .NET 2005.
The program was used to work properly with sql server 2000.
I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.
But the problem is when program executes something like this:
EXEC [AddOrder] ...
two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine.
I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.
Any ideas?
Kind Regards: Ali
View 6 Replies
View Related
Nov 8, 2006
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
Thanks,
Jim
View 4 Replies
View Related
Mar 1, 2007
I have a SP that inserts data into a table with an identity column I want to get that id and use it in my response.redirect however I keep getting 0 back instead of the identity. Relevent Sql 2005 Declare @NEWEMPID int --after an insert happens in sqlSet @NEWEMPID= SCOPE_IDENTITY()--last thing in my sqlReturn @NEWEMPID Or I tried NEWEMPID as an output parameter VB.Net Dim retValParam As New SqlParameter("@NEWEMPID", SqlDbType.Int) retValParam.Direction = ParameterDirection.ReturnValue'execute the sqldatasource insert command
sqlEmpNewEmp.Insert()
Dim EmpIDVar As Integer = Convert.ToInt32(retValParam.Value) Response.Redirect("http://www.mysite.com?EMPID=" + Server.HtmlEncode(EmpIDVar)) sqlEmpNewEmp.InsertParameters.Clear()
View 8 Replies
View Related