I have this SPROC that is executed by a SSIS every hour. Basically, between 1AM-6AM, I don't want the SPROC to execute what ever is inside. it still keeps running whatever is inside the IF statement.
TRUNCATE TABLE DataSubscription
DECLARE @StartTime AS VARCHAR(100)
DECLARE @EndTime AS VARCHAR(100)
SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'
IF GETDATE() NOT BETWEEN CAST(@StartTime AS DateTime) AND CAST(@EndTime AS DateTime)
BEGIN
-- Do stuff here.
END;
We have a request to build a report based on user input from an excel spreadsheet. We have a SSIS package that imports the data from Excel. This is run by a sql server agent job. Our stored procedure executes this job and runs this whole process just fine but when we execute the stored procedure from reporting services we get errors. Has anyone done this type of thing before and do you have any working solutions for how to get this reporting methodology to function?
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:
StudentInfo StudentID StudentName DeptID 101 John 10 102 Alex 10 103 Beth 20 ClassInfo ClassID DeptID 901 10 902 10 225 20 I want to create a Sproc which will retreive the student's classes in DeptID 10
Following is the Sproc and cursor:
use master go Create PROCEDURE [dbo].[getEnclishClasses] @StudentID int AS Declare @printInsertStatement nvarchar(100) ECLARE NewRowID int
Declare classCursor CURSOR FOR SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo WHERE StudentID=@StudentID)
DECLARE @ClassID INT DECLARE @DeptID INT
OPEN classCursor FETCH NEXT FROM classCURSOR INTO @ClassID, @DeptID WHILE (@@FETCH_STATUs=0) BEGIN PRINT 'SET @newID = Scope_Identity()' SET @printInsertStatement= (Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values(' +CONVERT(NVARCHAR (10), @ClassID) + ',' +CONVERT(NVARCHAR (2), @DeptID)+')' FROM [myTest].dbo.StudentInfo WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo WHERE StudentID=@StudentID))
PRINT @printInsertStatement END CLOSE classCursor DEALLOCATE classCursor EXEC getEnclishClasses 101
Here is what I try to get (text with actual data from the table): SET @newRowID = Scope_Identity() INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10) SET @newRowID = Scope_Identity() INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)
Here is what I had got (returning multiple lines, more than number of records I have): Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
I'm trying to use the SPROC below (courtesy of Erland!) to capture theerror message but it fails owing to insufficient permissions (I can'treproduce it just now, but I think it's because it can't get access tothe DBCC OUTPUTBUFFER).How do I give the SPROC permission to execute?Many thanksEdwardCREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT ASDECLARE @dbccrow nchar(77),@msglen int,@lenstr nchar(2),@sql nvarchar(2000),@s tinyint-- Catch the output buffer.CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)INSERT INTO #DBCCOUTEXEC ('DBCC OUTPUTBUFFER(@@spid)')-- Set up a cursor over the table. We skip the first-- row, because there is nothing of interest.DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FORSELECT col1FROM #DBCCOUTWHERE left(col1, 8) <> replicate('0', 8)ORDER BY col1-- Init variable, and open cursor.SELECT @errmsg = ''OPEN error_cursorFETCH NEXT FROM error_cursor INTO @dbccrow-- On this first row we find the length.SELECT @lenstr = substring(@dbccrow, 15, 2)-- Convert hexstring to intSELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT-- @s is where the text part of the buffer starts.SELECT @s = 62-- Now assemble rest of string.WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglenBEGINSELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +substring(@dbccrow, @s + 3, 1) +substring(@dbccrow, @s + 5, 1) +substring(@dbccrow, @s + 7, 1) +substring(@dbccrow, @s + 9, 1) +substring(@dbccrow, @s + 11, 1) +substring(@dbccrow, @s + 13, 1) +substring(@dbccrow, @s + 15, 1)FETCH NEXT FROM error_cursor INTO @dbccrowENDCLOSE error_cursorDEALLOCATE error_cursor-- Now chop first character which is the length, and cut after end.SELECT @errmsg = substring(@errmsg, 2, @msglen)GO
I was wondering if it is possible to execute a package inside a script. I know there are other ways to do this like using the "Execute Package Task" or using DTExec inside a .bat file but those options won't fulfill what I am trying to do. It looks as though this function should be able to do this
Microsoft.SqlServer.Dts.Runtime.Package.Execute()
but I can't seem to get this to work. Has anyone used this before??
I have other work-arounds if this is impossible but thought I would ask.
Hi, I'm trying to loop thru a table and insert records into another table in ssis. So far I have been able to get the data using a execute sql task set up to store the full result set into a variable called data. I then drug a foreach loop container out and selected the foreach ADO enumerator and used my variable data as the ADO object source variable. I then set up a new variable under variable mappings with index 0 to get the collection values. How do I take that variable and update another table using another sql task inside the foreach loop container? Is this possible?
in a SSIS 2012 pkg, I'm trying to specify a SELECT TOP ? myColumn FROM myTable inside an Execute SQL task, but unsuccessfully.Is it possible to parameterize the TOP clause?
Hello, i need to create temporary table inside SP. i having one string variable @strQuery which contain dynamic query inside SP. i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table. so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..." when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl' If i removed Hash then it works fine. even for double Hash also its work fine. but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this? its very urgent for me. thanks in advance.
I've created an SSIS package that contains a Sequence Container with TransactionOption = Required. Within the container, there are a number of Execute Package Task components running in a serial fashion which are responsible for performing "Upserts" to dimension and fact tables on our production server. The destination db configuration is loaded into each of these packages using an XML configuration file. The structure of these "Upsert" packages are nearly identical, while some execute correctly and others fail. Those that fail all provide the same error messages.
These messages appear during Pre-Execute
[Insert new dimension record [1627]] Error: The AcquireConnection method call to the connection manager "DW" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Insert new dimension record" (1627) failed the pre-execute phase and returned error code 0xC020801C.
... which are followed by
[Connection manager "DW"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".
[Connection manager "DW"] Error: An OLE DB error has occurred. Error code: 0x8004D00A.
While still in debug mode, I can check the properties of the "DW" connection and successfully test the connection within the packages that fail.
The same packages run successfully when tested outside the container (i.e. no transaction) or when the configuration file is modified to point the "DW" connection to a development version of the db which is running on the same server as the source database.
I have successfully used DTCtester to verify that transactions from source to destination server are working correctly. Also tried setting DelayValidation = True with no change. I have opened a case with Microsoft and am awaiting a reply so I thought I'd throw a post out here to see if anyone else has encountered this and might have a resolution. Here's some more on the environment:
Source Server:
Windows Server 2003 Enterprise Edition SP1 SQL Server 2005 Enterprise Edition SP0
Destination Server:
Windows Server 2003 Enterprise Edition SP1 SQL Server 2000 Enterprise Edition SP3 (clustered)
Thank you in advance for any feedback you might be able to provide.
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc: CREATE PROCEDURE [schemaname].[SetUserCulture] @UserID int , @Culture nvarchar(10) AS UPDATE dbo.SecUser SET Culture = @Culture WHERE UserID = @UserID
I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...
I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(
How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control..... No rows affected. (0 row(s) returned) No rows affected. (0 row(s) returned) Running [dbo].[sp_UPD_MESample_ACT_Formdata] ( @ME_Rev_Nbr = 570858 , @A1 = No , @A2 = No , @A5 = NA , @A6 = NA , @A7 = NA , @SectionA_Comments = none , @B1 = No , @B2 = Yes , @B3 = NA , @B4 = NA , @B5 = Yes , @B6 = No , @B7 = Yes , @SectionB_Comments = none , @EI_1 = N/A , @EI_2 = N/A , @UI_1 = N/A , @UI_2 = N/A , @HH_1 = N/A , @HH_2 = N/A , @SHEL_1 = 363-030 , @SHEL_2 = N/A , @SUA_1 = N/A, @SUA_2 = N/A , @Cert_Period = 10/1/06 - 12/31/06 , @CR_Rev_Completed = Y ).
No rows affected. (0 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[sp_UPD_MESample_ACT_Formdata]. The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0). And yet every time I try to update the record in the formview online... I get Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see. Does anyone have any tips or tricks or info that might help me?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks, Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry ( @EntryLogID int, @ProjectID int, @NewCategoryID int, @Expended decimal(10,2) ) AS DECLARE@OldCategoryID int
********************************************* BP_GetLedgerCategory ********************************************* ALTER PROCEDURE BP_GetLedgerCategory ( @EntryLogID int ) AS
SELECT CategoryID FROM BP_EntryLog WHERE EntryLogID = @EntryLogID
RETURN
********************************************* BP_UpdateCategories ********************************************* ALTER PROCEDURE BP_UpdateCategories ( @ProjectID int, @NewCategoryID int, @Expended decimal(10,2), @OldCategoryID int ) AS
UPDATE BP_Categories SET CatExpended = CatExpended + @Expended WHERE ProjectID = @ProjectID AND CategoryID = @NewCategoryID
UPDATE BP_Categories SET CatExpended = CatExpended - @Expended WHERE ProjectID = @ProjectID AND CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6)) as DECLARE @CityID integer declare @StateID integer declare @ZipID integer set @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null set @CityID= EXEC GetCityID(@City); set @StateID= EXEC GetStateID(@State); insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID) if @@ERROR = 0 SET @ZIPID = @@Identity select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs in the above stored proc? I mean what is the syntax??
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
I'm looking for a way to refer to a package variable within any Transact-SQL code included in either an Execute SQL or Execute T-SQL task. If this can be done, I need to know the technique to use - whether it's something similar to a parameter placeholder question mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements within the Execute SQL task, so I don't think the Execute T-SQL task is even necessary for this purpose.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID. What I have tried thus far: UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID) This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated. Thanks! Amanda
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something? SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle) ArticleID - int (PK) ArticleTitle - varchar ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect. but when using a sproc, i couldn't get anything The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text) AS SELECT ArticleID, ArticleTitle, ArticleContent FROM dbo.JournalArticle WHERE (ArticleAbstract LIKE @srch) GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything. I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC: ___________________ CREATE PROCEDURE dbo.sp_addMembershipRole @INCID Int AS declare @literal NVarChar (10) SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role]) VALUES (@INCID, @literal) GO ___________________
CODE: ___________________ using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Security.Cryptography; using System.Web.Security;
#region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click); this.Load += new System.EventHandler(this.Page_Load);
} #endregion
public void btnAdd_Click(object sender, System.EventArgs e) { int intContactID; string StrCID = Session["CID"].ToString(); intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text) { lblError.Text = "Retyping of your desired password did not match. Please try again."; return; }
create procedure GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint) as begin declare @ZipID integer declare @AddrID integer set @AddrID=1 if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2) return(@AddrID) end GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6)) AS BEGIN DECLARE @CityID integer DECLARE @StateID integer DECLARE @ZipID integer
SET @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'. This is my sproc ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT nvarchar(max) If @ClientId IS NULL Begin Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT)
