Regarding SQL Query - Database Discussion Boards

Re: Calling Web Service From SQL Stored Procedures

pmarfleet3-Apr-08 9:38

pmarfleet

3-Apr-08 9:38

You might be able to do this using CLR Stored Procedures[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Passing IN clause to SP via Parameter

stuebbie3-Apr-08 3:53

stuebbie

3-Apr-08 3:53

Hi,

I'm trying to send a parameter to a Stored Proc from ASP.NET in the form of a list of values to be used in an IN clause. However, the IN clause is looking up a column of type int, and I am getting a syntax error when I try to execute the SP:

C# code calling SP

<br />
<br />
SqlConnection conn      = new SqlConnection(sqlT.getServerConnString("ConnString"));            <br />
        SqlCommand cmd          = new SqlCommand();<br />
        cmd.Connection          = conn;<br />
        cmd.CommandType         = CommandType.StoredProcedure;<br />
        cmd.CommandText         = "SlideShowAppGenProp";<br />
        SqlParameter param1     = new SqlParameter();<br />
        param1.SqlDbType        = SqlDbType.VarChar;<br />
        param1.Size             = 1500;<br />
        param1.Value            = strPropRefList;<br />
        param1.ParameterName    = "@refNumber";<br />
        cmd.Parameters.Add(param1);<br />
<br />

In the above code, strPropRefList is a string value in the following format:

"129759, 130009, 130427, 130635, 130646, 131258"

SP code:

<br />
<br />
PROCEDURE SlideShowAppGenProp<br />
	<br />
	(<br />
	@refNumber varchar(1500)	<br />
	)<br />
	<br />
AS<br />
	SELECT<br />
	<br />
	a.ID AS 'refNumber',<br />
	c.Description AS 'Area',<br />
	d.Description AS 'Region'<br />
<br />
from<br />
	tPty_Property a<br />
	INNER JOIN tLkp_PropertyGuideDistrict c<br />
	ON a.lkpPropertyGuideDistrictID = c.ID<br />
	INNER JOIN tLkp_PropertyGuideSection d<br />
	ON a.lkpPropertyGuideSectionID = d.ID<br />
	<br />
WHERE <br />
	a.ID IN (@refNumber)<br />
<br />
ORDER BY<br />
	a.ID<br />
<br />

Running the code as it is, I get the following error...

Server: Msg 245, Level 16, State 1, Procedure SlideShowAppGenXml, Line 8
Syntax error converting the varchar value '129759, 130009, 130427, 130635, 130646, 131258' to a column of data type int.

...which is understandable because SQL is treating the whole value as one string, and places single quotes at each end.

I don't want to have to rework my C#/SQL code to make multiple DB calls (the number of reference values in the parameter string can go as high as 50), so is there another way that I can pass this comma-separated list of values into a SQL Stored Proc IN clause?

Apologies if there is a really simple solution and I have missed it.

Thanks,

Stuart

Re: Passing IN clause to SP via Parameter

Mark J. Miller3-Apr-08 4:13

Mark J. Miller

3-Apr-08 4:13

here's an article I wrote on doing exactly what you're asking: http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx[^]

Mark's blog: developMENTALmadness.blogspot.com

Re: Passing IN clause to SP via Parameter

stuebbie3-Apr-08 21:57

stuebbie

3-Apr-08 21:57

Thank you both for your suggestions Smile | :)

I tried Mark Miller's suggestion, and has done exactly what I needed, many thanks.

Re: Passing IN clause to SP via Parameter

SimulationofSai3-Apr-08 13:04

SimulationofSai

3-Apr-08 13:04

The simplest thing would be to convert the whole thing to dynamic sql and use sp_executesql.

declare @sql nvarchar(500)<br />
set @sql = '<br />
SELECT<br />
a.ID AS 'refNumber',<br />
c.Description AS 'Area',<br />
d.Description AS 'Region'<br />
from<br />
tPty_Property a<br />
INNER JOIN tLkp_PropertyGuideDistrict c<br />
ON a.lkpPropertyGuideDistrictID = c.ID<br />
INNER JOIN tLkp_PropertyGuideSection d<br />
ON a.lkpPropertyGuideSectionID = d.ID<br />
WHERE <br />
a.ID IN (' + @refNumber + ')<br />
ORDER BY<br />
a.ID'<br />
<br />
exec sp_executesql @sql<br />

Having said that, implementing Mark Millers method would be the ideal thing to do.

SG
Cause is effect concealed. Effect is cause revealed.

How to register custom control

lav naphade3-Apr-08 2:13

lav naphade

3-Apr-08 2:13

How to register custom dll into report.rdlc(report viewer)?
and how to custom function in to report.rdlc ?
plz send me reply
Thanks & Regards
Lav Naphade

lav naphade

Re: How to register custom control

pmarfleet3-Apr-08 9:34

pmarfleet

3-Apr-08 9:34

lav naphade wrote:
How to register custom dll into report.rdlc(report viewer)?
and how to custom function in to report.rdlc ?

Reading the documentation[^] would be a good place to start, no?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Regarding SQL Query

sandhya143-Apr-08 1:28

sandhya14

3-Apr-08 1:28

hi all

i have 3 tables as employees,medical,expenses

i need to take the information from all these 3 tables for the employees

Table1(employees)

EmployeeID DepartmentID
1 100
3 115

TAble2(medical)

EmployeeID ClinicID
1 7
3 11
4 15

Table3(expenses)

EmployeeID ExpenseID
1 5

3 10

now i should get all the details of the employees which is common in all the 3 tables

my answer should be like this:-

EmployeeID DepartmentID ClinicID ExpenseID
1 100 7 5
3 115 11 10

hw will i write a query to get these details?....

thanks in advance...

Re: Regarding SQL Query

Rob Philpott3-Apr-08 2:37

Rob Philpott

3-Apr-08 2:37

You just want a straightfoward inner join for that.

select a.Id, a.DepartmentId, b.ClinicId, c.ExpenseId from Table1 a join Table2 b on a.EmployeeId = b.EmployeeId join Table3 c on b.EmployeeId = c.EmployeeId

Regards,
Rob Philpott.

What is the diffrence between CTE(Common Table Expression) and Derived table

Krishnraj3-Apr-08 0:27

Krishnraj

3-Apr-08 0:27

Hi all,

Can anybody knows the diffrence between CTE and Derived table ?? Confused | :confused:

Thanks in advance

Rana Krishnraj

Re: What is the diffrence between CTE(Common Table Expression) and Derived table

Mark J. Miller3-Apr-08 4:50

Mark J. Miller

3-Apr-08 4:50

Just google "CTE derived table" and you'll get the link below and many more.

http://msdn2.microsoft.com/en-us/library/ms190766.aspx[^]

Mark's blog: developMENTALmadness.blogspot.com

select distinct, but only on one column

phimix2-Apr-08 21:50

phimix

2-Apr-08 21:50

Aloha,

I'm doing a car related website, where I need to fetch 3 cars similar to the one currently viewed by the user.

I would like to fetch 3 cars of different brands, but I can't make it happen Frown | :(

My query looks like this. It returns 3 similar cars, but they might all be from the same brand. How can I change my query, so the 3 cars will be from 3 different brands?

<br />
SELECT brand, model, topspeed, acceleration, hp, ABS(987-hp) AS 'nearest' <br />
FROM cars WHERE hp <> 987 AND cars.type = 'FHC' <br />
AND brand != 'Bugatti'<br />
ORDER BY nearest LIMIT 3;<br />

Thanks, Mads

Re: select distinct, but only on one column

Rob Philpott2-Apr-08 22:27

Rob Philpott

2-Apr-08 22:27

What database are you using? Is this going in a stored procedure?

I think you probably need to adopt an approach where do your query for just one car, then union it with a second query the same as the first by with an added condition to make sure the brand is different, and then again for a third time.

Make sense?

Regards,
Rob Philpott.

Re: select distinct, but only on one column

phimix2-Apr-08 23:12

phimix

2-Apr-08 23:12

Makes sense. It's a good idea, but it's no secret that I would prefer a single query.

I use MySQL, it's a normal SQL string.

How grant ALTER on all tables to an user?

blackjack21502-Apr-08 21:11

blackjack2150

2-Apr-08 21:11

Is there a way grant the right to alter any existing table for the current database to a specific user?

Thanks.

Re: How grant ALTER on all tables to an user?

Mark J. Miller3-Apr-08 4:47

Mark J. Miller

3-Apr-08 4:47

You could use GRANT ALTER TO [username]. However, that grants alter permission to all objects within the database, including functions, stored procs and views. I tried using ALTER ANY TABLE, but it is not a valid command. So as far as I can tell you need to explicitly grant ALTER permissions for each table and you will need to do it every time you add a new table.

You could write a script which loops through sys.objects and grants ALTER permission on each user table. But other than that there doesn't seem to be a way to do it and you're still stuck adding permissions each time a table is created.

Mark's blog: developMENTALmadness.blogspot.com

Re: How grant ALTER on all tables to an user?

blackjack21503-Apr-08 10:03

blackjack2150

3-Apr-08 10:03

Mark J. Miller wrote:
So as far as I can tell you need to explicitly grant ALTER permissions for each table and you will need to do it every time you add a new table.

I thought so, but I still had some hop left that I could avoid it.

Thanks for the explanation.

Retrieve the second biggest amount from a table

Daniel_Logan2-Apr-08 21:03

Daniel_Logan

2-Apr-08 21:03

How do I get the second most expensive call from my database. Currently I am using a Max(amount) statement, but as it turns out the dump of data I was given was just that, a dump, and they have put the total of all calls in my data as well. Can anyone give me some script to select the max amount, but the second biggest amount?

Thanks Smile | :)

modified on Thursday, April 3, 2008 3:12 AM

Re: Retrieve the second biggest amount from a table

blackjack21502-Apr-08 21:16

blackjack2150

2-Apr-08 21:16

I think it's something like

SELECT TOP 2 FROM ... WHERE ... ORDER BY Price<br />

And then you just read the second returned row.

See here:
http://authors.aspalliance.com/stevesmith/articles/sprowcount.asp[^]

Re: Retrieve the second biggest amount from a table

Daniel_Logan2-Apr-08 21:24

Daniel_Logan

2-Apr-08 21:24

Ok that's great ye, thanks, but how do I get it to NOT display the TOP 1 and only the Second one?

Re: Retrieve the second biggest amount from a table

Rob Philpott2-Apr-08 22:33

Rob Philpott

2-Apr-08 22:33

Try something like this:

declare @max int<br />
<br />
-- get the maximum value<br />
select @max = max(call) from table<br />
<br />
-- get the highest value below the maximum<br />
select top 1 * from table where call < @max order by call desc

Of course, if you have two calls which share the highest level, this returns the third.

Regards,
Rob Philpott.

Re: Retrieve the second biggest amount from a table

Daniel_Logan2-Apr-08 23:13

Daniel_Logan

2-Apr-08 23:13

yes thank you. I've done something similar. I selected the Top 2 and ordered them desc and then returned that value to another Top 1 sort and got the second highest. I do think yours is more effective though.

Thanks alot

how to import data from excel to sql server 2005

ashok@techxygen2-Apr-08 20:36

ashok@techxygen

2-Apr-08 20:36