December 1, 2009 at 2:10 pm
I have two tables I'm working with that I am having an issue with. (I pasted the schema at the bottom)
When I run this query:
declare @roles varchar(20);
set @roles = '3,4,5,6'
select e.entID, e.entShortName, p.enterpriseID
from permissions p inner join entity e on e.entID = p.entID
where roleCodeID in (@roles)
order by e.entShortName
I get this error:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '3,4,5,6' to data type int.
If I change @roles to just one numeric value, it's fine. The problem I'm facing is that this is the query that populates a dropdown on an aspx page and until I tried to simulate the query, I couldn't figure it out. Now that I tried it in SQL, it makes sense why it's not working, but I can't make it accept a string.
Any ideas?
CREATE TABLE [dbo].[Permissions](
[permissionID] [int] IDENTITY(1,1) NOT NULL,
[dnnUserID] [int] NULL,
[entID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[enterpriseID] [int] NULL,
[roleCodeID] [int] NULL,
[Active] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updatedDate] [datetime] NOT NULL CONSTRAINT [DF_Permissions_EffectiveDate] DEFAULT (getdate()),
CREATE TABLE [dbo].[Entity](
[entID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[entShortName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[entNumericID] [int] NOT NULL,
[orgID] [int] NOT NULL,
[regionID] [int] NOT NULL,
[portID] [int] NOT NULL,
[busTypeID] [int] NOT NULL,
[powProID] [int] NULL,
December 1, 2009 at 2:21 pm
That query will not work. Why and other info, its better you read Books Online.
Use have many options to work with of which the below is 2 ways.
select e.entID, e.entShortName, p.enterpriseID
from permissions p inner join entity e on e.entID = p.entID
where roleCodeID in (3,4,5,6)
order by e.entShortName
OR
Create Table #temp ( RoleCodeID INT)
Insert into #temp
select 3
Union
select 4
Union
select 5
Union
select
select e.entID, e.entShortName, p.enterpriseID
from permissions p
inner join entity e on e.entID = p.entID
Inner Join #temp Temp on p.roleCodeID = Temp.RoleCodeID
order by e.entShortName
I did not test the queries since you have not provided sample data for your 2 tables.
Edited : Realised that you are getting the value from a string from application in which case you can ignore my reply.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 2:28 pm
Matthew, what you will need to do, if you need to accept multiple roles like this in a stored procedure, is use some dynamic sql.
Considering your query, i've build a quick sample for you, but I did not test it:
declare @sql nvarchar(2000)
set @sql = 'select e.entID, e.entShortName, p.enterpriseID
from permissions p inner join entity e on e.entID = p.entID
where roleCodeID in (' + @Roles + ')
order by e.entShortName'
print @sql
--sp_executeSQL @sql -- Uncomment this only when you're sure the printed query works correctly.
This will permit you to receive multiple parameters that are numeric, but in a comma delimited string.
Hope that helps,
Cheers,
J-F
December 1, 2009 at 2:29 pm
yeah, sorry about that. I should probably post this on asp.net instead.
December 1, 2009 at 2:36 pm
Matthew Cushing (12/1/2009)
yeah, sorry about that. I should probably post this on asp.net instead.
Why? you got a working solution now. Just use dynamic SQL and add your parameter to the string. Then, be sure to print the text before trying to execute the query, for debug purposes, and you can select the information when you're sure the Statement is in a correct syntax.
If you are having problems using the dynamic sql in your procedure, please post your procedure, we'll try to help.
Cheers,
J-F
December 1, 2009 at 2:39 pm
This is the format I need to fit it in:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:AppDevConnectionString %>"
SelectCommand="select e.entID, e.entShortName, p.enterpriseID from permissions p inner join entity e on e.entID = p.entID where roleCodeID in (' + @userid + ') order by e.entShortName">
<SelectParameters>
<asp:SessionParameter Name="userID" SessionField="Roles" />
</SelectParameters>
</asp:SqlDataSource>
and Visual Studio is choking on the same problem, can't convert it to int.
December 1, 2009 at 2:45 pm
You have 2 solutions for this.
1. Can you try using the statement I've provided you directly in your XML, in the Select statement parameter? Sometimes, you can use more complex SQL, using declare parameters, and procedures.
2. Can you try to execute a stored proc from the select statement, passing the parameter, just for testing purpose, and then simply change the select query to a stored procedure, and it will fit your needs, well I think.
Have a try of 1 of these solutions, I think it has a chance of working.
Cheers,
J-F
December 1, 2009 at 2:52 pm
J-F Bergeron (12/1/2009)
You have 2 solutions for this.1. Can you try using the statement I've provided you directly in your XML, in the Select statement parameter? Sometimes, you can use more complex SQL, using declare parameters, and procedures.
2. Can you try to execute a stored proc from the select statement, passing the parameter, just for testing purpose, and then simply change the select query to a stored procedure, and it will fit your needs, well I think.
Have a try of 1 of these solutions, I think it has a chance of working.
I'm going to give #2 a shot, it hadn't even occurred to me to write a stored proc. Thanks!
December 1, 2009 at 3:10 pm
Tried executing this, but the result set comes back with 0.
ALTER PROCEDURE udsp_CPOChooseFacility
-- Add the parameters for the stored procedure here
@roles varchar(2000)
AS
BEGIN
-- Insert statements for procedure here
declare@sql nvarchar(2000)
set @sql = 'select e.entID, e.entShortName, p.enterpriseID
from permissions p inner join entity e on e.entID = p.entID
where roleCodeID in (' + @roles + ')
order by e.entShortName'
print @sql
END
GO
tried this:
declare @roles varchar(500)
set @roles = '0,1,2,3,4,5'
declare@sql nvarchar(2000)
set @sql = 'select e.entID, e.entShortName, p.enterpriseID
from permissions p inner join entity e on e.entID = p.entID
where roleCodeID in (' + @roles + ')
order by e.entShortName'
print @sql
and the result was a sql statement, that when I copied and pasted it into a query window, it worked fine. Sorry, I think I've written like 3 SP's in my life so I apologize if I'm missing something obvious.
December 1, 2009 at 5:45 pm
Not a problem...
Change this...
print @sql
... to this...
EXEC (@SQL)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2009 at 2:21 am
Let me recommend some excellent resources about using arrays as input parameters:
- http://www.sqlmag.com/Article/ArticleID/100657/sql_server_100657.html
- http://www.sqlmag.com/Article/ArticleID/100975/sql_server_100975.html
December 2, 2009 at 7:42 am
So, you used the stored procedure call in the select statement of the ASP.net, with the string parameter, and everything is fine? Good to know you can do that!
Cheers,
J-F
December 2, 2009 at 8:21 am
the way the sqlsource works in Visual Studio is you can either write your own sql, or hook in a stored proc. I wrote it, fixed it, and voila, we're back in business.
thanks so much for everyone's help.
December 2, 2009 at 8:57 am
Happy it works, thanks for the feedback!
Cheers,
J-F
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply