October 4, 2010 at 3:13 pm
Hi,
I need to pass a parameter into a stored procedure which values are dynamic always. Executing a variable which having the Select statement.
Following query working fine.
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
DECLARE @CountryId INT
DECLARE @CityId INT
SET @CountryId = 2
SET @CityId = ' AND CityId IN (23,45,85,86)'
SELECT @strQuery= 'SELECT VendorId From Vendors
WHERE CountryId = '+@CountryId+' '+@CityId+'
ORDERBY CreatedDate'
EXEC(@strQuery)
END
I need to execute above in an open select statement instead of executing @strQuery.
Getting error as When trying through following script as "Conversion failed when converting the varchar value ' AND CityId (23,45,85,86)' to data type int"
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDERBY CreatedDate
Please help me to get it working through above select statement.
Thanks
Sharma
October 4, 2010 at 3:40 pm
kuna.sharma (10/4/2010)
Hi,Getting error as When trying through following script as "Conversion failed when converting the varchar value ' AND CityId (23,45,85,86)' to data type int"
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDERBY CreatedDate
Please help me to get it working through above select statement.
Thanks
Sharma
Hi,
It seems as if you have declared the variable @CityId as Int, You should declare it as a varchar(24). If I counted correctly, that should be long enough. I think you need to add the keyword IN between AND and the opening paranthesis, which meens you need to define @CityId as varchar(27).
I hope it helps you to complete your query.
October 4, 2010 at 3:45 pm
Hi,
Thanks for your quick reply. Actually i declared that as varchar. Mistyped in the question as INT.
Any changes needed?
October 4, 2010 at 3:53 pm
This works for me fine...syntax-wise anyway. All I did was change the data types to nvarchar(50) (adjust to the proper sizes based on data).
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
DECLARE @CountryId NVARCHAR(50)
DECLARE @CityId NVARCHAR(50)
SET @CountryId = 2
SET @CityId = ' AND CityId IN (23,45,85,86)'
SELECT @strQuery = 'SELECT VendorId From Vendors
WHERE CountryId = ' + @CountryId + ' ' + @CityId + '
ORDERBY CreatedDate'
PRINT @strQuery
END
October 4, 2010 at 3:58 pm
October 4, 2010 at 3:59 pm
This way working but i need to execute the select statement directly i.e, without storing it in variable @strQuery then execution. I need to execute as follows:
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
DECLARE @CountryId INT
DECLARE @CityId NVARCHAR(50)
SET @CountryId = 2
SET @CityId = ' AND CityId IN (23,45,85,86)'
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDERBY CreatedDate
END
Actually im using a third party tool for reporting. That tool getting error for metadata if query execute through @strQuery. So trying now in open select statement.
Thanks
October 4, 2010 at 3:59 pm
INT mistype. In SP its VARCHAR
October 4, 2010 at 4:01 pm
October 4, 2010 at 4:05 pm
Hi All,
Thanks for your valuable inputs. Is following query is doable??
DECLARE @CountryId INT
DECLARE @CityId NVARCHAR(50)
SET @CountryId = 2
SET @CityId = ' AND CityId IN (23,45,85,86)'
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDER BY CreatedDate
October 4, 2010 at 4:08 pm
Change this:
SELECT @strQuery= 'SELECT VendorId From Vendors
WHERE CountryId = '+@CountryId+' '+@CityId+'
ORDERBY CreatedDate'
To this:
SELECT @strQuery= 'SELECT VendorId From Vendors WHERE CountryId = '
+CAST( @CountryId AS VARCHAR(100) )
+' '
+@CityId
+'ORDERBY CreatedDate'
I think, can't test because you already mentioned the sp has different datatypes then you're reporting here. Repost the code with the correct datatypes and I can actually test it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2010 at 4:20 pm
CREATE PROCEDURE spGetVendors
@CountryId INT,
@CityId NVARCHAR(50)
AS
SET NOCOUNT ON;
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDER BY CreatedDate
GO
October 4, 2010 at 4:38 pm
SP as follows:
CREATE PROCEDURE spGetVendor
@CountryId INT,
@CityId NVARCHAR(50)
AS
SET NOCOUNT ON;
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDER BY CreatedDate
GO
October 4, 2010 at 4:39 pm
SP attached
October 5, 2010 at 2:07 am
From looking at the attached procedure text, I don't think the procedure will work.
I wrote to possible solutions using the AdventureWorks2008 R2 database. I didn't write them as a Stored Procedure, but I think it should be possible to se how you could use my examples in our situation.
When it comes to query performance towards the database, I would suspect that my second example is best. But I'm not using a very elegant way of extracting the numbers from the input string.
How could I optimize this?
Anyway, these calls are working for me:
USE AdventureWorks2008R2
Go
DECLARE
@BusEntId varchar(50),
@queryText varchar(512)
SET @BusEntId = '(1, 2, 3, 4, 5)'
SET @queryText = 'SELECT *
FROM Person.Person
WHERE BusinessEntityID IN ' + @BusEntId
EXECUTE(@queryText)
Go
--- Second solution:
DECLARE
@busEntId varchar(50),
@queryText varchar(512),
@tempId INT
DECLARE @busEntIds TABLE
(
BusEntId INT
)
SET @BusEntId = '1, 2, 3, 4, 5'
WHILE ( LEN(@busEntId) > 0)
BEGIN
IF((CHARINDEX(',', @busEntId)) > 1)
BEGIN
SET @tempId = SUBSTRING(@busEntId, 1, CHARINDEX(',', @busEntId) - 1)
END
ELSE
BEGIN
SET @tempId = @busEntId
END
INSERT INTO @busEntIds(BusEntId)
VALUES(@tempId)
--Remove the newly identified bussinessEntityId from the input string
SET @busEntId = SUBSTRING(@busEntId, LEN(CAST(@tempId as varchar)) + 2, LEN(@busEntId))
--Remove any leading white spaces from the input string
SET @busEntId = LTRIM(@busEntId)
END
SELECT *
FROM Person.Person pp
INNER JOIN @busEntIds be on be.BusEntId = pp.BusinessEntityID
October 5, 2010 at 2:52 am
As you are using 2008 , why not use a TVP ? (Table Value Parameter)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply