Can A Value Be Assigned To An Output Parameter And Be Used In A Where Clause?

Dec 18, 2006

I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.Output
So when I update I assign the value to OfficeId like this (the office id value is being correctly assigned):
 
SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4);
            if (_officeId > 0) { OfficeIdParam.Value = _officeId; }
            OfficeIdParam.Direction = ParameterDirection.Output;

 
 The following segment of the stored procedure is throwing an error that the OfficeId does not exist:
 Update --- @OfficeId int  output
 
if not exists (select 1 from ORG_Office where OfficeId=@OfficeId)
    begin
        RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.
        return -1
    end
 
May this approach work, and if the operation turns out to be an Insert I may do this:
select @error_code = @@ERROR, @OfficeId= scope_identity()
 
Thank you,
jspurlin
 

View 1 Replies


ADVERTISEMENT

The Formal Parameter @ReportingId Was Not Declared As An OUTPUT Parameter, But...what Is This?

Apr 17, 2008

After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...

An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".

I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.

The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...




Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null

if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions






...later as part of an else it does the following...




Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end






...and before the return it's value is inserted to different tables.

View 5 Replies View Related

My Output Parameter Is Being Treated As An Input Parameter...why

Sep 25, 2006

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal.  I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening?  The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){  if (e.Exception == null)    {   string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString();    ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int

Set @stoptrans = 'NO'

/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )

IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN

IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END

IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END

IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END

ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum

SET @NewRetVal = 0
---Successful deletion
END
GO
 --------------------------------------------------------  When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

View 2 Replies View Related

OUTPUT Clause

Nov 7, 2007

Hi,

I'm copying records from table A to Table B. On "insert" being successful I want to delete the copied records from table A. I want to use one single transaction. Could I use OUTPUT to delete all records in table A which key matches the ones on "inserted" ?. I believe it could be possible but I have tried several ways without any luck.

INSERT INTO Table A (Field 1, Field 2)
OUTPUT (DELETE Table B WHERE Key = (SELECT Key FROM Inserted))
SELECT (Field1, Field 2) FROM TABLE B

Or something like that,

Thank you

View 1 Replies View Related

Question On Output Clause !

Feb 29, 2008

I would like to add more columns on output clause while insert....

Following are the table formats used here ...

Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)

Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable

Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>

Is there any way to achieve this....

Thanks...

View 1 Replies View Related

Question About OUTPUT Clause

Nov 14, 2006

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @insertedRecords

values('row 20')



select ID from @insertedRecords





TIA,



Barkingdog



.

View 3 Replies View Related

Question On Output Clause !

Feb 29, 2008

I would like to add more columns on output clause while insert....

Following are the table formats used here ...

Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)

Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2

select C1,C2 from MyTable

Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>


Is there any way to achieve this....

Thanks...

View 3 Replies View Related

SQL 2012 :: Output Clause With Update

Sep 26, 2014

I need to update the account_status and show the audit in the ssrs report.

I wrote the following query in the ssrs report but when I click on refresh fields it is throwing error incorrect syntax near output

Declare @username nvarchar(50)
Declare @UserStatus Table
(id bigint,
Username nvarchar(50),
Oldaccount_status varchar(255),
Newaccount_status varchar(255))

[Code] .....

View 2 Replies View Related

Transact SQL :: UPDATE With OUTPUT Clause

May 28, 2015

I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below

UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL

The above code is throwing an error as follows:

Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113

The multi-part identifier "s.TCK_NR" could not be bound.

View 4 Replies View Related

T-SQL (SS2K8) :: Get Output Of Procedure And Assign It To A Variable Used In WHERE Clause?

Mar 25, 2014

Get output of SQL Procedure and assign it to a variable used in WHERE Clause

Later I want to use this variable in my code in the WHERE Clause

Declare @ProjectNo nvarchar(10)

--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

SELECT ID from TABLEA where Project in (@ProjectNo)

How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

View 1 Replies View Related

SQL Server 2012 :: OUTPUT Clause Returning Wrong Row?

Nov 6, 2014

I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.

Consider this script:

declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)

select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e

[Code] ....

The code works -- that is, the duplicate row is deleted. However the output clause returns:

abcdern
123451

So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?

View 9 Replies View Related

OUTPUT @count =no Of Records Returnd By Dynamic Where Clause

Aug 18, 2005

Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.

How should the query be constructed, executed, and then the Count(*) value returned?

"WHERE <condition1> AND <condition 2> ;


"AND <condition 2> " may exist or may not exist in the query; it is dependant."

View 1 Replies View Related

Recovery :: UPDATE Table And OUTPUT Clause To Capture Some Of Columns

May 28, 2015

I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below

UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL

The above code is throwing an error as follows:

Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.

View 5 Replies View Related

Error While Using OUTPUT Clause - The Multi-part Identifier Could Not Be Bound

Jun 2, 2006

I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into:
 
CREATE TABLE Parent(
      ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      ParentName VARCHAR(50) NOT NULL)
GO
 
CREATE TABLE Child(
      ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      ParentID INT NOT NULL REFERENCES Parent(ParentID),
      ChildName VARCHAR(50) NOT NULL)
GO
 
INSERT INTO Parent(ParentName) VALUES('Parent 1')
INSERT INTO Parent(ParentName) VALUES('Parent 2')
GO
 
INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1')
INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2')
GO
 
At this stage, there Child table looks like:
 




ChildID

ParentID

ChildName


1

1

Child 1


2

1

Child 2
 
What I want to do is copy Parent 1€™s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query:
 
DECLARE @LinkTable TABLE (FromChildID INT, ToChildID INT)
 
INSERT INTO Child(ParentID, ChildName)
OUTPUT c.ChildID, inserted.ChildID INTO @LinkTable
      SELECT 2, c.ChildName
      FROM Child c
      WHERE c.ParentID = 1
 
SELECT * FROM @LinkTable
 
In the end I was expecting Child table to look like:
 




ChildID

ParentID

ChildName


1

1

Child 1


2

1

Child 2


3

2

Child 1


4

2

Child 2
 
and OUTPUT clause to return me:
 




FromChildID

ToChildID

 


1

3

Child record with ID 3 was created using ID of 1.


2

4

Child record with ID 4 was created using ID of 2.
 
 
But infact I€™m getting following error:
 
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "c.ChildID" could not be bound.
 
Any ideas on how to fix the OUTPUT clause in the query to return me the expected output?
 
Thanks
Yogesh

View 7 Replies View Related

Transact SQL :: Usage Of OUTPUT Clause Depending On Temporary Table

Jul 14, 2015

Suppose we have the following table in our database;

CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,

[code]....

This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;

command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";

well, not only this statement gives an error while executing; but also, no such usage defined in the

documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar

code]....

No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.

View 7 Replies View Related

Mapping A String Field To Boolean Output In SELECT Clause

Oct 18, 2006

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

View 3 Replies View Related

How To Use A SP Parameter In A 'where In' Clause?

Oct 20, 2006

I am trying to do a  'WHERE IN' clause by taking the value of a paremeter and using it as the value in the WHERE IN clause such as: DECLARE @parm NVARCHAR(1000) = 'IBM, MOT, MSFT'SELECT * FROM SomeStockTable WHERE TickerSymbol IN (@parm)I would like to avoid using an EXEC statement and string-building this whole query, since that is ugle and hard to maintain.ANy ideas? TYVM  

View 1 Replies View Related

Where Clause As A Parameter

Aug 22, 2007

I have posted this question in the MSDN SSRS forum, but have had no luck. I'm hoping someone can give me a definite yes or no if this is possible...

I want to be able to define a parameter for a report that will represent the entire where clause.

E.g. the report's dataset will be:
SELECT name, phone FROM person WHERE @whereclause

Has anyone accomplished this?

View 4 Replies View Related

Using 'IN' Clause As Parameter

Apr 13, 2008

I have the following query:






Code Snippet

create PROCEDURE [dbo].[usp_GetSearchedEvents]

(
@SpecialityID varchar(400),
@MinistryID varchar(400)
)


AS

SELECT Distinct Events2.EventName
FROM Events2
Where ((Events2.specialityID in (@SpecialityID)) or (Events2.MinistryID in (@MinistryID)))

Now if I fill in the the variables manually, i.e, in ('1','2') it returns data. But if I execute it and enter the values of the variables as so: '1',2' it doesnt return any results. How do I accomplish this? Thanks for any input.

View 14 Replies View Related

Is There Any Way To Use A Single Parameter For An IN Clause?

Jan 18, 2008

I have an application that needs user parameters for some reports, and it allows them to enter any amount of said parameter, so the query is built like this:"Select ... Where ParameterColumn IN (" + txtParameters.Text + ")";the parameters textbox only allows numbers and commas to be on the safe side of SQL injection. now, due to this the query has to be built dynamically in the code behind  page. is there any way to use a single parameter to add all of those values? and set the parameter to the text in the control during design mode to the datasource control?something like: Select ... Where ParameterColumn IN (@Parameters) <asp:ControlParameter ControlID="txtParameters" Name="Parameters" PropertyName="Text " />whenever I try that I get something along the lines of "Error converting nvarchar '1,2,3'  to datatype int"Thanks! 

View 3 Replies View Related

Can IN Clause Accept A Parameter Value?

Jun 19, 2000

I'm trying to write an SP which takes a string parameter like '1,3,6,9' and then uses it in an IN clause of a SELECT statement to return a recordset, along the lines of:

SELECT
custname
FROM
mytable
WHERE theid IN @myparameter

...any ideas how to get this kind of thing to work. In the example, theid is a numeric value. What it needs to do, I guess, is do a string replacement of the parameter value, but the SP naturally enough attempts to compare the numeric theid value with the string @myparameter value and fails. It works OK if there's only one value in the string, because the server auto-converts the paremeter into a numeric, but if there are 2 or more values in the string then the server obviously can't do the conversion. How can I make this work?

View 1 Replies View Related

Pass IN Clause Of Where As Parameter

Sep 29, 2005

HI

I want to Run a Dynamic SQL in which i need to pass IN parameters.

DECLARE @QUERY varchar(100)
DECLARE @Paremeter varchar(10)
SET @Parameter = '1,2'
SET @QUERY='SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (' + @parameter + ')'

But this causes an error that cant convert varchar string to int. Please dont suggest to avoid Dynamic Sql. I have to use it.

View 1 Replies View Related

Where Clause Parameter In Views

Jul 19, 2009

I have an Epicor ERP system that enables writing 'queries' in its internal format, that can see Views in the MSSQL database. Very useful, as I can write view definitions with much more flexibility than the ERP tools. As far as I can tell the ERP DB interface can't call SQL procedures.

What I want to to is to pass a value from the ERP engine that the View can use to restrict record selection. The View query produces a field that describes how far down into a nested parts list structure it has got, so the data is not in the database, it is calculated by the query.

A user may wish to restrict the depth of descent to, say 3 levels. I can filter the results set returned by the view in today's solution to only show levels up to 3, but the View still processes all levels (some 12 million records possible return) before the filtering can be applied. SLOW...

So I want to pass a parameter (if that is the right jargon) into the View to limit how much work it will do.

View 2 Replies View Related

Where Clause - Column Name As Parameter

Sep 20, 2014

In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?

So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?

I do not want to use dynamic SQL string concatenation...

View 5 Replies View Related

SPROC: 'LIKE' Clause And Parameter

Sep 15, 2005

Hi,

I've tried that but it does not work:

SELECT Field FROM Table WHERE Field LIKE @parameter



thanks

View 4 Replies View Related

Report Parameter As A Where Clause

Aug 21, 2007

Hello

Is it possible to construct a dataset where the parameter of the report is the where clause?

I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.

View 12 Replies View Related

How To Replace A Default Value With Parameter From A Where Clause

Jul 19, 2007

Table-Account0
column   EAIndex  ă€?BillerCodeă€?ChainCodeă€?PostDateă€?PostAmountă€?BalanceDateă€?BillBegDateă€?BillEndDateă€?BillCountă€?ChAmount
 Table-Biller
 CompanyCodeă€?BilerCodeă€?BillerNameă€?UniformNoă€?BusCodeă€?AcctNoă€?BillerInfoList
Table-DataBackup
column CompanyCode�Keepmonth
 in Table-Account0  , I use this sql query to retrieve data by column PostDate-3
Select * From ZT_EAccount0  Where isNumeric(PostDate) = 1 And PostDate < Convert(varchar(10),DateAdd(Month,-3,GetDate()),112)
now , I don't won't to lock the value "-3" , the subtract value will depend on the value of colum keepmonth from table DataBackup
can you please tell me how to do ?
 
thank you very much
 

View 4 Replies View Related

Stored Procedure Parameter And IN Clause

Dec 9, 2003

This works:

WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005'))


This doesn't:

WHERE
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears))


@strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?

View 4 Replies View Related

How To Add A Where Clause By Parameter In A Stored Procedure

Aug 1, 2005

What i want is to add by parameter a Where clause and i can not find how to do it!CREATE PROCEDURE [ProcNavigate]( @id as int, @whereClause as char(100))ASSelect field1, field2 from table1 Where fieldId = @id    /*and @WhereClause */GOany suggestion?

View 1 Replies View Related

String Parameter With Comma To Where Clause

Mar 20, 2014

I want to bring a string parameter to where clause like this:

select .. from ...
Where Code IN (@Code)

When @Code is 'ABC' it works but when @code is 'ABC, XYZ' it won't work. I know for IN statement we should use IN ('ABC', 'XYZ') and I tried make @code = '''ABC'', ''XYZ''' still not working. I tried

Print '''ABC'', ''XYZ'''

I got 'ABC', 'XYZ' but why it does not work in @Code?

View 4 Replies View Related

Passing Orderby Clause As Parameter In SP

Jan 24, 2008

Hi,

I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.

Below is my procedure..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),

as

BEGIN

SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END

The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.

I am not able to figure out why.

Any help in this will be appriciated.

Regards,
Salim.

View 16 Replies View Related

WHERE Clause Using Different Column Passed By Parameter

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

Getting Value From Output Parameter

Aug 2, 2006

I have an SQL INSERT statement with an output parameter called @CusRef. I have been trying to store this in a session variable, however all i am able to store is the reference to the parameter object. (The returned value stored in the parameter is an Integer)Session("CustomerReference") = DataConn.InsertParameters.Item("CusRef")I cant seem to find a value field or a method to get the value from a parameter. I have also tried using CType(DataConn.InsertParameters.Item("CusRef"), Integer) but it cant convert the Parameter type to an Integer.Please help,ThanksGareth

View 1 Replies View Related







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