Passing Parameters To Stored Proc From Crystal Reports

Hi,

I've written reports based on stored procedures that receive parameters
and it usually seems straight forward.

Today I would like to use Crystal Reports parameter interface t prompt
for a "Date Range" and then pass the lower and upper date values to the
@StartDate and @EndDate of my SPROC's range.

Unfortunately when I try to build a report off of a SPROC havng two
dates it insists on prompting for date AND time indivually.

Is there a way to disable the "automatic prompting", use my own Date
Range parameter and then pass the results of the date range to the
stored procedure?

Thanks



ADVERTISEMENT

Passing Parameters To Stored Proc

 

I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement  is :
 
Where actv.ProjID + '-' + actv.Activity =  @project
 
This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!

View Replies View Related

Passing A Comma Delimited String Of Parameters To A Stored Proc

Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
 
I considered using a hidden string parameter set = " ' " +  join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
 
Thanks.

View Replies View Related

Crystal Reports Parameters From One Form To Other

 

hi friends,
i am having one crystal report from and one web application from which contains one crystal reportviewer i need to give the parameter in the web form and get the data for that parameter untill this its working fine for me.
 
my problem i need to display the value in the crystal report which i gave in the web form.

View Replies View Related

Passing Multi-Valued Parameters Between Reports.

 

Hello,
 
I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
 
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
 
Can any one help me with this. Its very urgent.
 
Thanks,
SqlNew

View Replies View Related

SQL Stored Procedure And Crystal Reports 8.5

Hi guys!

I am new in Crystal reports so please help me on this one.

I have to create a report that will bring back the TOP 20 customers per Sales Rep. My SQL stored procedure needs a parameter @SalesRep to execute. My stored procedure links between 2 tables. How do I get this stored procedure executed in Crysal Reports to give me results. The SP works fine.

THANKS!!

View Replies View Related

SQL Server Stored Procedure With Crystal Reports

Hello,

I have a stored procedure in SQL Server that I am trying to attach to a Crystal Report. When I try to attach the procedure, I get the attached error message. I have few more procedure that are working correctly. The only difference between those and this one is a procedure call... in this procedure, I call another procedure.

Any help you could provide is appreciated. E-mail me if you need further information.

View Replies View Related

Adding Field To Stored Procedure For Crystal Reports

Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott

View Replies View Related

1 To 1, 1 To Many. Create One Stored Procedure For Crystal Reports(CR). For Any CR Users Also.

Situation:  If possible, create one stored procedure for Crystal Reports(CR).   For any CR users ou
there, looking for coding suggestion also.  Thank you for your assistance.
 
 Currently, CR has a header(Report Header) coming from 1 to 1 tables, there is a parameter
 which is passed in, allowing it to retrieve one record for the header(report header or RH).
 
 The CR then has 4 subreports in which each has its own stored procedure.  This I believe happens
 because the Report Header records relationship to the subreport is 1 to many.  The 3 remaining
 subreports relationship to the Report Header is also a 1 to many.  The main problem is the
 subreports is that there may or may not have any records based on this, the subreport is
 suppressed within CR and thus there can be alot of unused white space on the 1st page and one
 of the subreports prints on a 2nd page when it could have been on the 1st page.
 
 Example:
 
 Main Stored Procedure(sp)
 RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.
 
 Results of subreports and their associated procedure are varchar(8000) decriptions.  Each line
 should be counted in some manner in the stored procedure(sp) and then should be counted in CR
 to avoid excess white space.  To  complicate matters subreport 2 to has font, bold, showbox but
 can  have different font sizes.    These variations could cause different line space requirements.
 Any ideas?

 
 Each line should be counted in some manner in the stored procedure and then should be counted
 in CR.  There is a count of records for each the main stored procedure.
 
 RH has 1 record to many records in subreport 1 with same @xyz parameter. 
 RH has 1 record to many records in subreport 2 with same @xyz parameter.
 RH has 1 record to many records in subreport 3 with same @xyz parameter.
 RH has 1 record to many reords in subreport 4 with same @xyz parameter.
 
 Because of the relationships, its seems impossible to create one stored procedure which give
 in one select statement with all the 1 to 1, 1 to many relationsips, as stated above.  I thought
 concatinating i.e. 3 records together and then parsing it out some how in CR, along with
 utilizing the i.e. 3 record count to help count lines.  Thought of some how creating a temporary
 table matrix for 8pt - 28pt for line and spacing considerations.
 
 
 
 

 

 

View Replies View Related

Stored Procedure Results Differ In ISQL/W And Crystal Reports

We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :

sp executes spA,spB and spC
Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.

In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.

We use SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we get now rows in Crystal.

I enclose an example (with 3 sub- SPs). Help. Martin


CREATE PROCEDURE coral.qryPayEmp8_newtmp @lQryCompanyNumber int,@dtQryPeriodFromDate datetime,@dtQryPeriodEndDate datetime,@dtQryPayrollYearStartDate datetime,@szQryCompanyTaxReference varchar(30) AS
--variables used for cursors
DECLARE
@lCCUniqueID INT,
@lUniqueID INT,
@lEmployeeNumber INT,
@szEmployeeSurname VARCHAR (20),
@szInitials VARCHAR (4),
@szDeptNo VARCHAR (6),
@dtDateLeft DATETIME,
@EdTotalGrossPayThis FLOAT,
@EdTaxableGrossPayThis FLOAT,
@EdTotalGrossPayPrevious FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
@EdTaxPaidThis FLOAT,
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate FLOAT,
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),

--output from qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
@SumOfdEmployeeNIContractedOut FLOAT,

--output from qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT

SET NOCOUNT ON

DECLARE employee_period CURSOR
FOR
SELECT tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,
tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,
tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,
tblEmployees.dTotalGrossPayThis,
tblEmployees.dTaxableGrossPayThis,
tblEmployees.dTotalGrossPayPrevious,
tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,
tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate
FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)


DECLARE employee_ni_categories SCROLL CURSOR
FOR
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,
tblEmployeePeriodDetails.cNICategory
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))

OPEN employee_period

FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8b @lUniqueID,
@dtQryPayrollYearStartDate,
@SumOfdEmployersNICHoliday OUTPUT
EXEC qryPayEmp8c @lUniqueID,
@dtQryPeriodEndDate,
@PdTotalGrossPayThis OUTPUT,
@PdTaxableGrossPayThis OUTPUT,
@PdTaxPaidThis OUTPUT,
@PdSMPPaid OUTPUT,
@PdSSPPaid OUTPUT
OPEN employee_ni_categories
FETCH FIRST FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
IF (@@FETCH_STATUS <> -1)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8a @lUniqueID,
@szNICategory,
@dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,
@SumOfdEmployersNI OUTPUT,
@SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT

SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid,
@szNICategory AS szNICategory,
@SumOfdNIablePay AS SumOfdNIablePay,
@SumOfdEmployersNI AS SumOfdEmployersNI,
@SumOfdEmployeeNI AS SumOfdEmployeeNI,
@SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings,
@SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut

FETCH NEXT FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
END
ELSE
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid
CLOSE employee_ni_categories
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
END

CLOSE employee_period
DEALLOCATE employee_period

DEALLOCATE employee_ni_categories
RETURN

-----------------


CREATE PROCEDURE coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) = 'A',
@dtPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT OUTPUT,
@SumOfdContractedOutEarnings FLOAT OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),
@SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),
@SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),
@SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s),
@SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory = @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory

RETURN

-----------------------------

REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT = 1,
@dtQryPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT
@SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))


RETURN

--------------------

CREATE PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT = 1,
@dtQryPeriodEndDate DATETIME = '1900-01-01 00:00:00.000',
@PdTaxableGrossPayThis FLOAT OUTPUT,
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET NOCOUNT ON
SELECT @PdTotalGrossPayThis = tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis = tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis = tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid = tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid = tblEmployeePeriodDetails.dSSPPaid

FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))


RETURN

View Replies View Related

1 T0 1 , Then 1 To Many Records(4x's). Create One Stored Procedure For Crystal Reports Usage.

Situation:  If possible, create one stored procedure for Crystal Reports(CR).   For any CR users out there, looking for coding suggestion also.  Thank you for your assistance.  
 
 Currently, CR has a detail line, I reference it here as Report Header but it is actually on
 the detail  line, coming from 1 to 1 tables, there is a parameter  which is passed in, allowing
 it to retrieve one record for the header(report header or RH).
 
 The CR then has 4 subreports in which each has its own stored procedure.  This I believe
 happens because the Report Header records relationship to the subreport is 1 to many.  The
 3 remaining subreports relationship to the Report Header is also a 1 to many.  The main
 problem is the subreports is that there may or may not have any records based on this, the
 subreport is suppressed within CR and thus there can be alot of unused white space on
 the 1st page and one of the subreports prints on a 2nd page when it could have been on the
1st page.
 
 Example:
 
 Main Stored Procedure(sp)
 RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.
 
 Results of subreports and their associated procedure are varchar(8000) decriptions.  Each
 line  should be counted in some manner in the stored procedure(sp) and then should be
 counted in CR  to avoid excess white space.  To  complicate matters subreport 2 to has font,
 bold, showbox but  can  have different font sizes.    These variations could cause different
 line space requirements.  Any ideas?

 
 Each line should be counted in some manner in the stored procedure and then should be
 counted  in CR.  There is a count of records for each the main stored procedure.
 
 RH has 1 record to many records in subreport 1 with same @xyz parameter. 
 RH has 1 record to many records in subreport 2 with same @xyz parameter.
 RH has 1 record to many records in subreport 3 with same @xyz parameter.
 RH has 1 record to many reords in subreport 4 with same @xyz parameter.
 
 Because of the relationships, its seems impossible to create one stored procedure which give
 in one select statement with all the 1 to 1, 1 to many relationsips, as stated above.  I thought
 concatinating i.e. 3 records together and then parsing it out some how in CR, along with
 utilizing the i.e. 3 record count to help count lines.  Thought of some how creating a
 temporary table matrix for 8pt - 28pt for line and spacing considerations.

View Replies View Related

Passing Value From Stored Proc To Another

Guys
I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)

create proc upGetDates
as
declare @start datetime ,
@end datetime

select@start = '2001-01-01' ,
@end = '2007-12-31'
go

I want to reuse this proc many times for different reports and using the date values in the calling sp

an example in pseudo code would be

create proc usedates
as
declare@rstart datetime ,
@rend datetime

exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc
select * from tablename where datecreated >= @rstart and datecreated < @rend
go

Not sure how to 'trap' the dates from the called proc

Can it be done ?

Thanks in anticipation

View Replies View Related

Passing A Column Into A Stored Proc?

I'm writing a simple voting script and have columns for each options.  I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement?  There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance 

View Replies View Related

Passing Xml To Stored Proc Issues.

Hi,

   I am passing a xml data to a stored proc on SQL2005 using ADODB.

  The XML is pretty short.

'<AMS><DeviceList><Device AmsTag="PDT1" /><Device AmsTag="PDT2" /></DeviceList></AMS>'

   Ado Command.Execute generates a Com Exception.

System.Runtime.InteropServices.COMException (0x80040E14): UTF-8   at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options)  at ALMFile.Form3.Form3_Load(Object sender, EventArgs e) in C:AMSCodesrcprasraoALMUtilsALMFileALMFileForm3.cpp:line 55

The parameter in the stored proc is set to NText while on the ADO side it iset to ADODB.DataTypeEnum.adVarChar

I have tried searching for the meaning of the msg but could not find it.

Interstingly if I execute the SP on the server and pass the data thru Exec , it works fine.

Also the VS2005 if I step into proc with same data it works fine.

This has me totally confused.

Prasanna

 

 

 

 

 

 

View Replies View Related

SQL2005 Passing GETDATE() As A Stored Proc Parameter

What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!

View Replies View Related

Passing Boolean To Stored Proc As SQLDBtype.bit Not Working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.

View Replies View Related

Passing Multiple Selections To A Stored Proc Parameter

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END
GO


I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_cri

View Replies View Related

Passing Table Variable To Stored Proc / Function

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View Replies View Related

Passing Datetime Variable To Stored Proc As Parameter

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task).  The variables are set in a previous task where they act as OUTPUT paramters from a stored proc.  The variables are set correctly after that task executes.  The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View Replies View Related

URGENT: REPORTS Not Working In SQL Report Srvr &&amp; Crystal Reports (moved Server)

 
Reports not working at all:
Need to move full ASp.Net application(VS.Net 2003) /SQL Server 2000 DB to another machine. The Web application & its SQL Database is successfully moved and functioning but am unable to access any of the reports from within it. The
 
reports are in: A) SQL server report server & B) Crystal reports
 
For A)-SQL SRVR-RPT SRVR: Installed SQL Server2005  Express version- with reporting srvr (advance mgt). The report server is working well- from IIS i can open both http://..reports and http://...reportserver links without error.
But when i click on a link in the ASP.Net application it gives error: /HRNE_REPORTS/rpt_employees does not exist( rsitemnotfound) .
1.In http://..reports configration I have made a folder HRNET_REPORTS. But how do i put the individual reports? There is no .RDL file in the application folder only .aspx files for the reports exist.
 
2.Do i need to restore the two reporting server databases from the old machine to the new one and have this new 2005 reporting server use these 2 databases instead? If YES  - then i hope sql express' reporting server will be able to point to the OLD (sql 2000) database (restored with replace option)??
 
For B)- CRYSTAL REPORTS: Have not installed anything special on the new machine.
The reports give some key problem (Crystal decisions...key..)- I can tell u later the exact error.
Since i dont have VS.Net and crystal reports 9 softwares but i think i can have 'Busines Objects XI software' - Will installing the latest 'Business Objects- Crystal reports' solve this??NEW MACHINE: windows XP-professional(so could'nt install sql 2000).; SQL express 2005

View Replies View Related

Passing Parameters Into Stored Procedure

I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors.  How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server"            ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink"            SelectCommandType="StoredProcedure">            <SelectParameters>                        <asp:Parameter Name="Zip" Type="string" />             <asp:Parameter Name="MenuCode" Type="double" />                               </SelectParameters>
 

View Replies View Related

Passing Parameters To A SQL Stored Procedure

I need help with a really basic task.
I have a formview that works OK for selecting/updating records in the table.  When the user edits the data and clicks submit, the updates post to the table along with two parameter values I add using this in the code-behind:
Protected Sub DS_EditMasterOrg_Updating1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles DS_EditMasterOrg.Updating
e.Command.Parameters("@Last_Update_By").Value = User.Identity.Name
e.Command.Parameters("@Last_Update_Dt").Value = System.DateTime.Now
End Sub
The next step, where I am stuck, is if the data is OK and does not need to be updated I have a button the user can click to indicate that fact.  I am trying to use the button's onclick event handler to trigger a stored procedure to update those two fields in the record.  The stored procedure expects these two parameters and executes correctly when they are supplied.  I can't figure out the correct syntax of the statements to plug into the onclick event handler that will send the two parameters to the SP.  What I have so far is simply this (which obviously doesn't work):
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sqlConnection1 As New Data.SqlClient.SqlConnection("Data Source=GV88Z91;Initial Catalog=contests;Integrated Security=True")
Dim cmd As New Data.SqlClient.SqlCommand
Dim rowsAffected As Integer
Dim MOID As String = Profile.MasterOrgId
Dim user As String = Context.User.Identity.Name
With cmd
.CommandText = "Director_VerifyMasterOrg"
.CommandType = System.Data.CommandType.StoredProcedure
.Connection = sqlConnection1
.Parameters.Add("MOID")
.Parameters.Add("user")
.Parameters("MOID").Value = Profile.MasterOrgId
.Parameters("User").Value = Context.User.Identity.Name
End With
sqlConnection1.Open()
rowsAffected = cmd.ExecuteNonQuery()
sqlConnection1.Close()
 
Thanks in advance for the assistance.

View Replies View Related

Passing Parameters To Stored Procedures

Hello,

I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.

I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.

Thanks for your help, Gary

Here is the web code:


Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)

cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))

cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue

conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()


Here is the stored procedure:



CREATE PROCEDURE s_ItemLookUp

@Field AS VARCHAR(50),
@Value AS VARCHAR(50)

AS


SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO

View Replies View Related

Passing Parameters To A Stored Procedure

Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)

The value I'm passing (@MyLSN) is 24/12359 (one LS Number)

The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))

The value I'm passing (@MyLSN) is '24/12359','24/12360'

This is where the problem begins because the stored procedure now doesn't return a recordset.

If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.

So if someone could point me in the right direction then that would be much appreiciated.

Thanks in advance.

View Replies View Related

Passing Parameters To Stored Procedure Using ADO

I have created a form that contains a textbox (txtState), button (cmdRun), and listbox (lstResults). I want to use the txtState textbox to supply a parameter to a stored procedure named (test), and then execute the stored procedure by clicking the (cmdRun) button which will display the results in the lstResults listbox.

I have looked around everywhere for a nice simple answer, but only seem to find complex answers. Could someone please provide a nice simple solution. Here is the code for my stored procedure. It's the ADO code that I am having a great deal of trouble with.

create procedure "test" @s nvarchar(2)
as
select states from customers
where states = @s

View Replies View Related

Passing Parameters To A Stored Procedure

Hey people,

I am trying to pass a parameter to a stored procedure based on a select statement. Basically it's something like this (in my head :P ):

foreach PersonID in (SELECT id FROM person)
{
       MyStoredProcedure(PersonID);
}

Multiple rows will be returned from the select statement.

The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?

Thanks to you all!

View Replies View Related

Comparison Of SQL Server Reporting Services To Access Reports, Crystal Reports, Cognos Or Other Options

Hello SQL Server Experts, Data Analysts, and Report Writers et al:

re: Reporting Options with SQL Server

I wanted to propose an offshoot to the pryor thread:

Would anyone take a stab at comparing Access Reports, Crystal Reports,
Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?

I guess this is a far as capabilites, ease of use, limitations, and especially formatting
or presentation of the end report product?

Thank you to all, and I hope this is a beneficial discussion to others.

Hal1490



Hal9000

View Replies View Related

Too Many Parameters In Stored Proc?

I am creating a stored proc in SQL7 with my front end coded in VB6Pro. My
question is this: How many parameters can be passed to a stored proc before
performance really suffers? I know, in theory, it doesn't matter... I have
16 parameters that the stored proc will evaluate and insert into a table.
Would there be a more efficient way for me to do this? I need to enforce
several rules through this SP, and the insertions must be compatible with
both my front end app, as well as my web site...

What do you all think?

Thank you very much.

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved