Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Execute Stored Procedure

Author  Topic 

MARINOS
Starting Member

13 Posts
Posted - 2008-06-14 : 10:06:10
How can I use in stored procedure the faction ‘’in’’ to select values, using execute and the values must not be specified before in the stored procedure

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2008-06-14 : 10:46:35
Could you elaborate a bit more? It is not clear what you are trying to say.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-14 : 10:54:21
You cant use exec stored procedure in in part of a query. You need to first get the result set of sp onto a temporary table having same structure as result set and then use temp table in in clause. Something like:-

INSERT INTO #Temp
EXEC SP @Param1=value1,@Param2=value2,...

SELECT ....
FROM YourTable
WHERE field in (SELECT field FROM #Temp)
Go to Top of Page

MARINOS
Starting Member

13 Posts
Posted - 2008-06-14 : 10:55:31
how can i use in stored pocedure ''in'' like :

WHERE T_StockNoteItem.NoteItemType = 1
AND dbo.T_StockNote.NoteType =1
AND T_StockNote.NoteBasicType = 2
AND T_StockNote.NoteIssueTime BETWEEN @FromDate AND @TillDate
AND T_Category.CategoryId BETWEEN @FromCategoryId AND @TillCategoryId
AND T_StockNote.StoreId BETWEEN @FromStoreId AND @TillStoreId

and replace between with in faction
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-14 : 11:00:07
quote:
Originally posted by MARINOS

how can i use in stored pocedure ''in'' like :

WHERE T_StockNoteItem.NoteItemType = 1
AND dbo.T_StockNote.NoteType =1
AND T_StockNote.NoteBasicType = 2
AND T_StockNote.NoteIssueTime BETWEEN @FromDate AND @TillDate
AND T_Category.CategoryId BETWEEN @FromCategoryId AND @TillCategoryId
AND T_StockNote.StoreId BETWEEN @FromStoreId AND @TillStoreId

and replace between with in faction

Didnt get what you're asking. Can you illustrate with some sample data?
Go to Top of Page

MARINOS
Starting Member

13 Posts
Posted - 2008-06-14 : 11:06:32
@FromDate smalldatetime = NULL,
@TillDate smalldatetime = NULL,
-- @FromSupplierId int = 0,
-- @TillSupplierId int = 0,
@FromCategoryId int = NULL,
@TillCategoryId int = NULL,
@FromStoreId int = NULL,
@TillStoreId int = NULL


AS

SET @FromCategoryId = ISNULL (@FromCategoryId,0)
SET @TillCategoryId = ISNULL (@TillCategoryId,0)
SET @FromStoreId = ISNULL (@FromStoreId,0)
SET @TillStoreId = ISNULL (@TillStoreId,0)
--SET @FromSupplierId = ISNULL (@FromSupplierId,0)
--SET @TillSupplierId = ISNULL (@TillSupplierId,0)



SELECT StoreDescr,
ItemId,
ItemDescr,
SubGroupDescr,
GroupDescr,
CategoryId,
CategoryDescr,
-- SupplierDescr,
SUM (ISNULL(PurchasesQty,0)) AS 'PurchasesQty',
SUM (ISNULL(PurchasesValue,0)) AS 'PurchasesValue',
SUM (PurchasesValue) / SUM (PurchasesQty)AS AveragePurchasePrice

FROM
(
SELECT T_Store.StoreDescr as 'StoreDescr',
T_Item.ItemId AS 'ItemId',
T_Item.ItemDescr AS 'ItemDescr',
T_SubGroup.SubGroupDescr AS 'SubGroupDescr',
T_Group.GroupDescr AS 'GroupDescr',
T_Category.CategoryId AS 'CategoryId',
T_Category.CategoryDescr AS 'CategoryDescr',
-- dbo.V_DispSuppliers.PersonDescription AS 'SupplierDescr',

(CASE WHEN dbo.T_TransType.TransTypeCancel = 0
THEN dbo.T_StockNoteItem.NoteItemQuantity
WHEN dbo.T_TransType.TransTypeCancel = 1
THEN -1* dbo.T_StockNoteItem.NoteItemQuantity
ELSE 0 END)AS 'PurchasesQty',

(CASE WHEN dbo.T_TransType.TransTypeCancel = 0
THEN dbo.T_StockNoteItem.NoteItemCostValue - dbo.T_StockNoteItem.NoteItemReductionValue
WHEN dbo.T_TransType.TransTypeCancel = 1
THEN -1*(dbo.T_StockNoteItem.NoteItemCostValue - dbo.T_STockNoteItem.NoteItemReductionValue)
ELSE 0 END)AS 'PurchasesValue'


FROM T_StockNoteItem
LEFT OUTER JOIN T_StockNote
ON T_StockNote.StockNoteId = T_StockNoteItem.StockNoteId
LEFT OUTER JOIN T_TransType
ON T_TransType.TransTypeId = T_StockNote.TransTypeId
LEFT OUTER JOIN T_Item
ON T_Item.ItemId = T_StockNoteItem.ItemId
LEFT OUTER JOIN T_SubGroup
ON T_SubGroup.SubGroupId = T_Item.PurchaseSubgroupId
left outer join T_Group
on T_Group.GroupId = T_SubGroup.GroupId
left outer join T_Category
on T_Category.CategoryId = T_Group.CategoryId
LEFT OUTER JOIN T_Store
ON T_Store.StoreId = T_StockNote.StoreId
-- LEFT OUTER JOIN dbo.V_DispSuppliers
-- ON dbo.V_DispSuppliers.SupplierId = dbo.T_StockNote.SupplierId

WHERE T_StockNoteItem.NoteItemType = 1
AND dbo.T_StockNote.NoteType =1
AND T_StockNote.NoteBasicType = 2
AND T_StockNote.NoteIssueTime BETWEEN @FromDate AND @TillDate
AND T_Category.CategoryId BETWEEN @FromCategoryId AND @TillCategoryId
AND T_StockNote.StoreId BETWEEN @FromStoreId AND @TillStoreId
-- AND T_StockNote.SupplierId BETWEEN @FromSupplierId AND @TillSupplierId

) AS VT

WHERE PurchasesQty > 0 AND PurchasesValue >0


GROUP BY StoreDescr,
ItemId,
ItemDescr,
SubGroupDescr,
GroupDescr,
CategoryId,
CategoryDescr
-- SupplierDescr

how can i use in replasing between and exec te procedure from a software application
ex. from the software a i like to slece storeid 2,3,5,7 etc how can i use this
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-14 : 14:10:15
quote:
Originally posted by MARINOS

@FromDate smalldatetime = NULL,
@TillDate smalldatetime = NULL,
-- @FromSupplierId int = 0,
-- @TillSupplierId int = 0,
@FromCategoryId int = NULL,
@TillCategoryId int = NULL,
@FromStoreId int = NULL,
@TillStoreId int = NULL


AS

SET @FromCategoryId = ISNULL (@FromCategoryId,0)
SET @TillCategoryId = ISNULL (@TillCategoryId,0)
SET @FromStoreId = ISNULL (@FromStoreId,0)
SET @TillStoreId = ISNULL (@TillStoreId,0)
--SET @FromSupplierId = ISNULL (@FromSupplierId,0)
--SET @TillSupplierId = ISNULL (@TillSupplierId,0)



SELECT StoreDescr,
ItemId,
ItemDescr,
SubGroupDescr,
GroupDescr,
CategoryId,
CategoryDescr,
-- SupplierDescr,
SUM (ISNULL(PurchasesQty,0)) AS 'PurchasesQty',
SUM (ISNULL(PurchasesValue,0)) AS 'PurchasesValue',
SUM (PurchasesValue) / SUM (PurchasesQty)AS AveragePurchasePrice

FROM
(
SELECT T_Store.StoreDescr as 'StoreDescr',
T_Item.ItemId AS 'ItemId',
T_Item.ItemDescr AS 'ItemDescr',
T_SubGroup.SubGroupDescr AS 'SubGroupDescr',
T_Group.GroupDescr AS 'GroupDescr',
T_Category.CategoryId AS 'CategoryId',
T_Category.CategoryDescr AS 'CategoryDescr',
-- dbo.V_DispSuppliers.PersonDescription AS 'SupplierDescr',

(CASE WHEN dbo.T_TransType.TransTypeCancel = 0
THEN dbo.T_StockNoteItem.NoteItemQuantity
WHEN dbo.T_TransType.TransTypeCancel = 1
THEN -1* dbo.T_StockNoteItem.NoteItemQuantity
ELSE 0 END)AS 'PurchasesQty',

(CASE WHEN dbo.T_TransType.TransTypeCancel = 0
THEN dbo.T_StockNoteItem.NoteItemCostValue - dbo.T_StockNoteItem.NoteItemReductionValue
WHEN dbo.T_TransType.TransTypeCancel = 1
THEN -1*(dbo.T_StockNoteItem.NoteItemCostValue - dbo.T_STockNoteItem.NoteItemReductionValue)
ELSE 0 END)AS 'PurchasesValue'


FROM T_StockNoteItem
LEFT OUTER JOIN T_StockNote
ON T_StockNote.StockNoteId = T_StockNoteItem.StockNoteId
LEFT OUTER JOIN T_TransType
ON T_TransType.TransTypeId = T_StockNote.TransTypeId
LEFT OUTER JOIN T_Item
ON T_Item.ItemId = T_StockNoteItem.ItemId
LEFT OUTER JOIN T_SubGroup
ON T_SubGroup.SubGroupId = T_Item.PurchaseSubgroupId
left outer join T_Group
on T_Group.GroupId = T_SubGroup.GroupId
left outer join T_Category
on T_Category.CategoryId = T_Group.CategoryId
LEFT OUTER JOIN T_Store
ON T_Store.StoreId = T_StockNote.StoreId
-- LEFT OUTER JOIN dbo.V_DispSuppliers
-- ON dbo.V_DispSuppliers.SupplierId = dbo.T_StockNote.SupplierId

WHERE T_StockNoteItem.NoteItemType = 1
AND dbo.T_StockNote.NoteType =1
AND T_StockNote.NoteBasicType = 2
AND T_StockNote.NoteIssueTime BETWEEN @FromDate AND @TillDate
AND T_Category.CategoryId BETWEEN @FromCategoryId AND @TillCategoryId
AND T_StockNote.StoreId BETWEEN @FromStoreId AND @TillStoreId
-- AND T_StockNote.SupplierId BETWEEN @FromSupplierId AND @TillSupplierId

) AS VT

WHERE PurchasesQty > 0 AND PurchasesValue >0


GROUP BY StoreDescr,
ItemId,
ItemDescr,
SubGroupDescr,
GroupDescr,
CategoryId,
CategoryDescr
-- SupplierDescr

how can i use in replasing between and exec te procedure from a software application
ex. from the software a i like to slece storeid 2,3,5,7 etc how can i use this

Where's the sample data as asked? No use posting your queries as still dont know how ur data is and what you're trying to get out of them. post the data and give output you expect out of them
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site