Is using a SP return value bad technique?

I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?
Thanks,
SteveUsing a return value to return simple integer scalal values is *THE* way to do what you want.

Returning a scalar values using a recorset with just one row and one column is too expensive.

You cannot return "Yes" or "No" with return values anyway, just integers are allowed. If you need to return "yes" or "no" in a string format use output values.|||My fault. That was a complete lapse of brainpower on my part. What you described is exactly what I meant to say(either a 1 or 0 for true or false). Oh well. That's what I get for working on a Saturday.

Thanks,
Steve|||Using a return value to return simple integer scalal values is *THE* way to do what you want.

Absolutely not.

Use an ouput variable and leave the return code alone...

Even if you specify

Return -1

For example, SQL Server in some cases can and will override the value...

So if you code for it, it could be a problem.|||Brett I've never had any problem using return values. Even BOL doesn't mention it. That would be awful! :)

Anyway what i wanted to evidence is that returning as scalar value in a recordset is a bad idea. Some more info here:

http://www.sqlteam.com/item.asp?ItemID=2644|||Yeah, I remeber Bills article.

But it was after a long thread that I think Arnold or Nigel identied/explained the problem.

I then went on and posted an example of where the return value was over ridden, making an output variable the only safe way.

I should blog that one...|||Well, surely it'll be an interesting read. Please do it.

Btw this "feature" seems to be more a bug than anything else...isn't it?|||Here's the thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35642sql

Is this the best way to do this ?

I have a query that has several optional fields to filtered the returned data from. They can either be null or have a value. This is the query that I have written. It works fine for filtered the data. But for some reason the query only ever returns 9 rows of data. No matter what parameter values I pass for @.startRowIndex and @.maximumRows.

Help.

SELECT CustomerName, Filename, UserName, DateAdded, PhotoID
FROM (SELECT CustomerName, Filename, UserName, DateAdded, PhotoID, ROW_NUMBER() OVER (ORDER BY Filename) AS RowNum
FROM (SELECT DISTINCT Photos.CustomerName, Photos.Filename, Photos.UserName, Photos.DateAdded, Photos.PhotoID
FROM Photos INNER JOIN
IndustryCatalog ON Photos.PhotoID = IndustryCatalog.PhotoID INNER JOIN
OptionCatalog ON Photos.PhotoID = OptionCatalog.PhotoID
WHERE (Photos.CustomerName LIKE '%' + @.CustomerName + '%' OR
@.CustomerName IS NULL) AND (Photos.UserName LIKE '%' + @.UserName + '%' OR
@.UserName IS NULL) AND (Photos.State LIKE '%' + @.State + '%' OR
@.State IS NULL) AND (Photos.City LIKE '%' + @.City + '%' OR
@.City IS NULL) AND (Photos.WorkOrderNumber = @.WorkOrder OR
@.WorkOrder IS NULL) AND (Photos.Series = @.Series OR
@.Series IS NULL) AND (Photos.ColorID = @.ColorID OR
@.ColorID IS NULL) AND (Photos.StructureWidth = @.StructureWidth OR
@.StructureWidth IS NULL) AND (Photos.StructureLength = @.StructureLength OR
@.StructureLength IS NULL) AND (IndustryCatalog.IndustryID = @.IndustryID OR
@.IndustryID IS NULL) AND (IndustryCatalog.AppID = @.AppID OR
@.AppID IS NULL) AND (OptionCatalog.CategoryID = @.CategoryID OR
@.CategoryID IS NULL) AND (OptionCatalog.OptionID = @.OptionID OR
@.OptionID IS NULL) AND (Photos.Country LIKE '%' + @.Country + '%' OR
@.Country IS NULL) AND (Photos.PhotoFinishNumber = @.PhotoFinishNumber OR
@.PhotoFinishNumber IS NULL) AND (Photos.Description LIKE '%' + @.Description + '%' OR
@.Description IS NULL) AND (Photos.Resolution > @.Resolution OR
@.Resolution IS NULL)) AS FilteredPhotos) AS Paged
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1

Hi, I simplied you query and did a test in my database, the 2 parameters (@.startRowIndex and @.maximumRows) did work. There must be some other thing that caused the 2 parameters ineffective. Have you set the ROWCOUNT option? You can turn off the option by using this statement:

SET ROWCOUNT 0

|||Turns out I was using the wrong type of join in my query and thats what was screwing up my sql... Thanks for hte help though.

Is this SQL stored prodcedure is valid

what i want to achive is the proc sh'd return a master-detail value in one go.
master value should be returned with Out Parameter and detail value as a recordset.

will it return the recordset of detail table as below...

Create Procedure ProductDetail
(
 @.ProductID int,
 @.ProductCode varchar(15) OUTPUT,
 @.ProductName varchar(60) OUTPUT,
 @.CategoryID int OUTPUT,
 @.CategoryName varchar(60) OUTPUT,
 @.Image1 varchar(256) OUTPUT,
 @.Image2 varchar(256) OUTPUT,
 @.UnitPrice smallmoney OUTPUT,
 @.UOMValue numeric(9) OUTPUT,
 @.UOMName varchar(10) OUTPUT,
 @.ShippingWeight numeric(9) OUTPUT,
 @.Directions varchar(1500) OUTPUT,
 @.Ingrediants varchar(1500) OUTPUT,
 @.Warnings varchar(1500) OUTPUT,
 @.ShortDescription varchar(1000) OUTPUT,
 @.LongDescription varchar(2000) OUTPUT,
 @.NutritionFacts varchar(1000) OUTPUT,
 @.SearchKeywords varchar(500) OUTPUT,
 @.IsTaxable varchar(15) OUTPUT,
 @.CreatedBy varchar(60) OUTPUT,
 @.CreatedOn varchar(15) OUTPUT,
 @.UpdatedBy varchar(60) OUTPUT,
 @.UpdatedOn varchar(15) OUTPUT,
 @.Status int OUTPUT
)
AS
SELECT
 @.ProductCode = ProductCode,
 @.ProductName = ProductName,
 @.CategoryID = CategoryID,
 @.CategoryName = (select CategoryName from mCategory where CategoryID=a.CategoryID),
 @.Image1 = isnull(Image1,''),
 @.Image2 = isnull(Image1,''),
 @.UnitPrice = isnull(UnitPrice,0),
 @.UOMValue = isnull(UOMValue,0),
 @.UOMName = isnull(UOMName,''),
 @.ShippingWeight = isnull(ShippingWeight,0),
 @.Directions = isnull(Directions,''),
 @.Ingrediants = isnull(Ingrediants,''),
 @.Warnings = isnull(Warnings,''),
 @.ShortDescription = isnull(ShortDesc,''),
 @.LongDescription = isnull(LongDesc,''),
 @.NutritionFacts = isnull(NutritionFacts,''),
 @.SearchKeywords = isnull(SearchKeywords,''),
 @.IsTaxable = case when isnull(IsTaxable,0)=0 then 'No' else 'Yes' End,
 @.CreatedBy = isnull((select LName + ',' + FName from mUser where UserID=InsertedBy),''),
 @.CreatedOn = InsertedOn,
 @.UpdatedBy = isnull((select LName + ',' + FName from mUser where UserID=UpdatedBy),''),
 @.UpdatedOn = UpdatedOn,
 @.Status = Convert(int,isnull(Status,0))
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
BhairavI believe the way you are doing it is possible, but why not return two recordsets back to a dataset? Then you would have a master datatable and detail datatable. I believe this would work:

Create Procedure ProductDetail
(
 @.ProductID int
)
AS
SELECT
 ProductCode,
 ProductName,
 CategoryID,
 (select CategoryName from mCategory where CategoryID=a.CategoryID),
 isnull(Image1,''),
 isnull(Image1,''),
 isnull(UnitPrice,0),
...
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

HTH|||thks for u'r suggesion
but can u plz explain me in more detail ...
how my dataset code will look like when a single strore proc return more than one recordset.

proc must not be called more than once for that...

Regards,
Bhairav|||Sure,
Just follow the code (I'm using Microsoft Data Access Application Blocks to call):

SqlParameter [] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@.ProductID", SqlDbType.Int);
arParms[0].Value = 1;
DataSet myDS = SQLHelper.ExecuteDataset("connectiion", StoredProcedure, "ProductDetail", arParms);
DataTable myTable1 = myDS.Tables[0];
DataTable myTable2 = myDS.Tables[1];

That should give you an example of the calling code. There are other ways to manipulate the dataset data. If you are unfamiliar, just hollar and we can give you some direction, or search the archives of the data access forms. HTH|||thks..
its really the nice way to code
thks again

Regards,
Bhairav

Is this possible?

hey guys,

I have a column called Error Count, which display the the error count values (Fields!error_count.value) from the dataset. And assume the report has some parameters.I want to change the values of this column by comparing the parameters value that the user specified with the values in the database. For instance, If sessions.timestamp == parameters!date.value then do something, where timestamp is a field in the sessions table and parameters!date.value is the the parameter value.

Please let me know if anybody came across this kind of situation and how you solve it.

Any idea is appreciated

Sincerely

Amde

Two approaches come to mind for accomplishing this.

The first way is to use a stored procedure, where you pass the report parameters to the stored procedure. In the stored procedure you have complete control over what values get put in the dataset.

The second approach would be to use a custom data processing extension. You could change the contents of the .Net data set returned by your sql query before returning it to reporting services. Or, another way would be to change the values on the fly when reporting services asks for a particular field from the data set.

I suggest using the first way, as it will require less code and infrastructure complexity.

Is this possible?

Hello,

My question relates to the following select statement:

Select Report_description from Report where Report_name = (grab this value from the item selected from a listbox)

I wonder whether it would be possible to make the above statement a stored procedure but instead of filling in the last value in the bracket, I would like to grab that value from else where, for example from an item from a listbox which has been selected by the user.

Hi is Dude

we use this n number of time. The thing you need to do is, just create the comma separated value of the selected item list in the front end.

As an Example

list selected values as

'i','am','a',boy' (you need to do this in the front end itself)

in query do like this

Select Report_description from Report where Report_name in('i','am','a',boy')

you need to use the IN operator to select the selected values for the Report table

Regards,

Thanks.

Gurpreet S. Gill

|||

Hi Gill,

Its great to know that this can be done. Unfortunately I am a newbie to all this. Could you please elaborate? For example if I was using ASP.NET, and I suppose alll this code would go into the code behind file of the list box control? So what would the code actually look like? And would I just leave the last value in the stored procedure as a blank space?

Thank you so much

|||

I cant say much about the ASP.NET, but this code works for me.

here the ListBox1 is the List box from where you want to collect the values, CSV is string variable, used in IN clause of SQL

Try this

Dim CSV As String, SQL As String, i As Integer

CSV = ""

'Loop to all the Items in the ListBox1

For i = 0 To ListBox1.Items.Count - 1

'Check if selected or not

If ListBox1.Items(i).Selected Then

' if selected, make the comma separated value single Quote around it

CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "

End If

Next

' Ignore the last extra comma

CSV = Left(CSV, Len(CSV) - 3)

' Create the SQL command

SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"

' Your codes goes here

' Use the SQL variable to execute the query

'

Kiind Regards,

Gurpreet S. Gill

|||

ohhhh, PLEASE IGNORE THIS post twice same

Dim CSV As String, SQL As String, i As Integer

CSV = ""

'Loop to all the Items in the ListBox1

For i = 0 To ListBox1.Items.Count - 1

'Check if selected or not

If ListBox1.Items(i).Selected Then

' if selected, make the comma separated value single Quote around it

CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "

End If

Next

' Ignore the last extra comma

CSV = Left(CSV, Len(CSV) - 3)

' Create the SQL command

SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"

' Your codes goes here

' Use the SQL variable to execute the query

'

Kind Regards,

Gurpreet S. GIll

|||thank you very much gill!!!

Is this possible?

Lets say I have an integer value 2002012, I want to convert it to a string so I can cut the value 4 spaces so that it reads 2002 and then convert it back to an int to add one to it. The name of the field is PERIOD.

CONVERT(Int, SUBSTRING(CAST(PERIOD As Varchar),1,4)) + 1

Is that possible at all or will I get an error?

Is that possible at all or will I get an error?

Give a try, then. It's easy, isn't it?

|||

This is also possible

declare @.i int
select @.i = 2002012

select left(@.i,4) +1

they will both work see below

declare @.i int
select @.i = 2002012

select left(@.i,4) +1,CONVERT(Int, SUBSTRING(CAST(@.i As Varchar),1,4)) + 1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

is this basic syntax correct?

Hi all,
I am trying to perform what would seem to be a very simple conditional check
but it never works!
I always end up with an empty value.
=SUM(IIF(Fields!Ticket.Value = 'Student', Fields!Quantity.Value =0,
Fields!Quantity.Value))
What I am hoping for in the result is that if there is a 'Student' in the
result set, when totalling, it should set that Quantity value to 0, but
continue to sum up the other values.
Any help appreciated.
ImmyTry
=SUM(IIF(Fields!Ticket.Value = "Student", 0,
Fields!Quantity.Value))
or
create a calculated field with
=IIF(Fields!Ticket.Value = "Student", 0,
Fields!Quantity.Value) as the expression and then sum the calculated
field.
Enter make sure to use double quotes SQLRS is real picky.

Is This a Correct Method ?

Hi All,
While inserting date value I wish to take only date part So I tried
this
Create Table JTrial
(
XYZ int,
d datetime default convert(varchar,getdate(),112)
)
insert into JTrial(XYZ) values(1)
insert into JTrial(XYZ) values(2)
insert into JTrial(XYZ) values(3)
insert into JTrial(XYZ) values(4)
select * from JTrial
Is there any better alternative. Check constraint like this
check ( d = convert(varchar,d,112) )
Will not allow me to insert row
insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has
time part
Is writing A trigger better alternative ?
Please guide me on this ?
With warm regards
Jatinder SinghYes, that is the method that I prefer (although I always specify a length fo
r varchar, see your
convert function). I also like to have a check constraint instead of a trigg
er. I have elaborated a
bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124441020.097121.43310@.f14g2000cwb.googlegroups.com...
> Hi All,
> While inserting date value I wish to take only date part So I tried
> this
> Create Table JTrial
> (
> XYZ int,
> d datetime default convert(varchar,getdate(),112)
> )
> insert into JTrial(XYZ) values(1)
> insert into JTrial(XYZ) values(2)
> insert into JTrial(XYZ) values(3)
> insert into JTrial(XYZ) values(4)
> select * from JTrial
> Is there any better alternative. Check constraint like this
> check ( d = convert(varchar,d,112) )
> Will not allow me to insert row
> insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has
> time part
> Is writing A trigger better alternative ?
> Please guide me on this ?
> With warm regards
> Jatinder Singh
>|||Hi Tibor,
The default constraint work with a value (fix sort of/ not entered by
user) of date and Check constraint does not let it pass if it has any
time other than (00:00:00), Both in what your article suggest and what
I tried.
Thanks for giving valuable advice , I purposed Trigger because I
would storage there.
Create Table JTrial
(
XYZ int,
d datetime default convert(varchar,getdate(),112)
)
Go
Create trigger trg1 on JTrial for Insert
as
Begin
Update JTrial set d=convert(varchar,d,112)
-- I should have take a cross join with the Inserted table
End
Declare @.aDate datetime
select getdate()
set @.aDate = '2005-08-19 18:17:09.607'
insert into JTrial(XYZ,d) values(1,getdate())
insert into JTrial(XYZ,d) values(2,@.aDate) -- User entered value
insert into JTrial(XYZ) values(3) -- Default will be stored
insert into JTrial(XYZ) values(4)
select * from JTrial
Drop table JTrial
With warm regards
Jatinder Singh
Tibor Karaszi wrote:
> Yes, that is the method that I prefer (although I always specify a length
for varchar, see your
> convert function). I also like to have a check constraint instead of a tri
gger. I have elaborated a
> bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1124441020.097121.43310@.f14g2000cwb.googlegroups.com...|||Jatinder,
If I understand you correctly, you are saying that a trigger has the possibl
e advantage of changing
the datetime value that the user entered so that it always has 00:00:00 as t
he time portion. Where a
check constraint will produce an error.
Yes, that is a correct observation. You can't say that one approach is alway
s correct. I prefer the
check constraint, as you will catch where applications is sending an invalid
datetime value and fix
the application. IMO, that is a better approach to just changing the value w
ithout the user or
client application programmer knowing you have changed it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124455793.019933.100900@.g43g2000cwa.googlegroups.com...
> Hi Tibor,
> The default constraint work with a value (fix sort of/ not entered by
> user) of date and Check constraint does not let it pass if it has any
> time other than (00:00:00), Both in what your article suggest and what
> I tried.
> Thanks for giving valuable advice , I purposed Trigger because I
> would storage there.
> Create Table JTrial
> (
> XYZ int,
> d datetime default convert(varchar,getdate(),112)
> )
> Go
> Create trigger trg1 on JTrial for Insert
> as
> Begin
> Update JTrial set d=convert(varchar,d,112)
> -- I should have take a cross join with the Inserted table
> End
> Declare @.aDate datetime
> select getdate()
> set @.aDate = '2005-08-19 18:17:09.607'
> insert into JTrial(XYZ,d) values(1,getdate())
> insert into JTrial(XYZ,d) values(2,@.aDate) -- User entered value
> insert into JTrial(XYZ) values(3) -- Default will be stored
> insert into JTrial(XYZ) values(4)
> select * from JTrial
> Drop table JTrial
> With warm regards
> Jatinder Singh
> Tibor Karaszi wrote:
>|||Sorry Typo
inner join in trigger
With warm regards
Jatinder Singh|||Tibor,
Thanks for giving your time. Is speed /performance an issue
here means using trigger v/s Conversions at client side.
With warm regards
Jatinder Singh|||Yes, passing in the correct data to begin with will give better performance
compared to having a
trigger which goes back to the modified rows and alter the value to the desi
red value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124457867.638474.309950@.g14g2000cwa.googlegroups.com...
> Tibor,
> Thanks for giving your time. Is speed /performance an issue
> here means using trigger v/s Conversions at client side.
> With warm regards
> Jatinder Singh
>

Is there one collation that includes all the Eastern Europen Languages and Latin 1 charset.

Can I specify a collate value for a column in a table that includes all the possible languages in the world or atleast Latin 1 and Eastern European languages.

My DB Collation is set to Latin 1 and the columns in the tables are all nvarchar or ntext, but certain hungarian characters are not displayed correctly.

What do all these collation codes represent:

SQL_EBCDIC037_CP1_CS_AS

211

SQL_EBCDIC273_CP1_CS_AS

212

SQL_EBCDIC277_CP1_CS_AS

213

SQL_EBCDIC278_CP1_CS_AS

214

SQL_EBCDIC280_CP1_CS_AS

215

SQL_EBCDIC284_CP1_CS_AS

216

SQL_EBCDIC285_CP1_CS_AS

217

SQL_EBCDIC297_CP1_CS_AS

They seem generic. Is there one collation that includes all the Eastern Europen Languages and Latin 1 charset. Please let me know.

Thanks,

Manisha

The collation affects sorting and code page for non-Unicode data. Since you are using Unicode strings (nvarchat & ntext) - the collation only affects sorting, it does not affect the character display.

If a character is not displayed correctly, it means either the data was converted to non-Unicode somewhere (check all the column types in SSIS) or the font does not support this character. You are not telling us where the character is displayed, so I have no idea which font are you using.

To answer original question - no, there is no universal collation, as each culture has its own sorting rules. But most probably the collation is not the problem here.|||The character is displayed on the webpage (jsp) where it is displayed incorrectly. However in the database when I open table it displays perfectly. I realize that there is no universal collation then it is probably a font issue as indicated, but I am being told by our front end developer that we are using Verdana font for display and that includes Hungarian Chars.|||I think the problem is not collation, but the code pages that front end developers use. The only common used encodings that includes all languages are Unicode and UTF-8, ask front end devs to generate their pages using UTF-8, and properly annotate them (I can't help you there, don't know JSP at all, seek a better forum).|||Thanks. We got this fixed yesterday by specifying the contentType on the JSP page level.

is there no way ?

is there no way i could retrieve value from a trigger variable to my
dataset
i mean ive been on it like a hound for two days and not a single
article or post that really helped me.
heres what i have understood till now.
ALTER TRIGGER lastserial
ON dbo.master
FOR INSERT
AS
begin
select serialcode = @.@.IDENTITY
end
now how do i retrieve this serialcode value to my application in vb.netHere is one way:
=====
CREATE TABLE Foo
(
colA VARCHAR(10)
)
GO
CREATE TRIGGER FooTrigger ON Foo
FOR INSERT AS
BEGIN
SELECT 'Srinivas'
END
GO
=====
The above creates a SQL Server table and a trigger on the same. Now, here is
the C#.NET code that puts a record into the table and reads the output of
the trigger into the application code and prints it out. The example uses
.NET 1.1 version
=====
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
using (SqlConnection oConn = new
SqlConnection("Server=tl- devdb\\matrix;Database=pubs;Uid=sa;Pwd=p
@.ssw0rd"))
{
SqlCommand oCmd = new SqlCommand();
SqlDataReader rdr;
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = "INSERT INTO Foo VALUES ('Sampath')";
oConn.Open();
try
{
rdr = oCmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine("{0}", rdr.GetString(0));
rdr.Close();
}
catch (SqlException e)
{
Console.WriteLine (e.Message);
}
finally
{
oConn.Close();
}
Console.Read();
}
}
}
}
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<prabodhtiwari@.gmail.com> wrote in message
news:1140425436.598510.175750@.f14g2000cwb.googlegroups.com...
> is there no way i could retrieve value from a trigger variable to my
> dataset
> i mean ive been on it like a hound for two days and not a single
> article or post that really helped me.
> heres what i have understood till now.
> ALTER TRIGGER lastserial
> ON dbo.master
> FOR INSERT
> AS
> begin
> select serialcode = @.@.IDENTITY
> end
> now how do i retrieve this serialcode value to my application in vb.net
>|||but how do you retrieve the value 'sampath' only using the datareader|||I thought your question was to retrieve what the trigger was SELECTing into,
which is what this example shows.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23m7Nc4fNGHA.1460@.TK2MSFTNGP10.phx.gbl...
> Here is one way:
> =====
> CREATE TABLE Foo
> (
> colA VARCHAR(10)
> )
> GO
> CREATE TRIGGER FooTrigger ON Foo
> FOR INSERT AS
> BEGIN
> SELECT 'Srinivas'
> END
> GO
> =====
> The above creates a SQL Server table and a trigger on the same. Now, here
> is the C#.NET code that puts a record into the table and reads the output
> of the trigger into the application code and prints it out. The example
> uses .NET 1.1 version
> =====
> using System;
> using System.Data;
> using System.Data.SqlClient;
> namespace ConsoleApplication1
> {
> class Class1
> {
> [STAThread]
> static void Main(string[] args)
> {
> using (SqlConnection oConn = new
> SqlConnection("Server=tl- devdb\\matrix;Database=pubs;Uid=sa;Pwd=p
@.ssw0rd")
)
> {
> SqlCommand oCmd = new SqlCommand();
> SqlDataReader rdr;
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.Text;
> oCmd.CommandText = "INSERT INTO Foo VALUES ('Sampath')";
> oConn.Open();
> try
> {
> rdr = oCmd.ExecuteReader();
> while (rdr.Read())
> Console.WriteLine("{0}", rdr.GetString(0));
> rdr.Close();
> }
> catch (SqlException e)
> {
> Console.WriteLine (e.Message);
> }
> finally
> {
> oConn.Close();
> }
> Console.Read();
> }
> }
> }
> }
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> <prabodhtiwari@.gmail.com> wrote in message
> news:1140425436.598510.175750@.f14g2000cwb.googlegroups.com...
>

is there no mod operator in analysis services ?

I am missing a mod (modulo, %) operator in analysis services.

I need to find out if a number is even or not ?
(to return the value in the middle of a set) - and if the tupples in the set is event then the average of the two in the middle.

HANNES

Hi Hannes,

Unfortunately, the MDX language in AS 2000 lacked a "mod" operator, and I'm not aware of it being added in AS 2005, either. Of course, it would be great if someone could contradict; meanwhile, I use this technique:

A Mod B ==> A - (Int(A/B) * B)

Is there anything similar in TSQL to oracle code "SEQUENCE.NEXTVAL

Hello,
I trying to do a SELECT that will show me the next value automatically
everytime.
for example in Oracle, I can do
Select SEQUENCE.NEXTVAL from Dual
1
Select SEQUENCE.NEXTVAL from Dual
2
Select SEQUENCE.NEXTVAL from Dual
3
with Oracle there is a function that allows me to use auto increment value
without specifying for insert into the table.
Can I do the same with a TSQL? Anything in TSQL close to this function.
Please help. Thanks again.No there isn't a function that can do this. But can you tell what is the
actual requirement, so that we can give you an alternative
--
"sqlapprentice" wrote:

> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||You can look into using IDENTITY, but it really depends on what you are
trying to accomplish.
"sqlapprentice" <sqlapprentice@.discussions.microsoft.com> wrote in message
news:C5A27B55-64ED-468F-A61B-5DC4715687BE@.microsoft.com...
> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||in SQL server, one uses the IDENTITY attribute of a column to specify
what would be similar to an Oracle Sequence.
CREATE TABLE mytable(
entryid INT IDENTITY(1,1),
entrydata VARCHAR(100)
)
Use the following to get the current value of the "Sequence"
DBCC CHECKIDENT ('owner.tablename')
in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
and also check out DBCC CHECKIDENT.|||"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1146499671.388074.220710@.i39g2000cwa.googlegroups.com...
> in SQL server, one uses the IDENTITY attribute of a column to specify
> what would be similar to an Oracle Sequence.
> CREATE TABLE mytable(
> entryid INT IDENTITY(1,1),
> entrydata VARCHAR(100)
> )
> Use the following to get the current value of the "Sequence"
> DBCC CHECKIDENT ('owner.tablename')
> in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
> and also check out DBCC CHECKIDENT.
>
besides DBCC CHECKINDENT, you can also use
select ident_current('table')
or
select max(identitycol) from table
ident_current() is the preferred method of the two.
dean

Is there any way that you could search for a value in any nodes within your xml data type?

Hi everyone,

I was wondering if there is any way that you could search for a value in any nodes within your xml data type without actually knowing what nodes you have or how many there are.

Please provide sample query for it if possible. Thanks.

declare @.x xml

set @.x = '<root><x>Test1</x><y>Test2<z>Test3</z></y></root>'

select @.x.query('//*[text()="Test1"]')

select @.x.query('//*[text()="Test2"]')

select @.x.query('//*[text()="Test3"]')

Results:

<x>Test1</x>

(1 row(s) affected)

-

<y>Test2<z>Test3</z></y>

(1 row(s) affected)

--

<z>Test3</z>

(1 row(s) affected)

|||

An open ended querying of xml that has some "value" isn't trivial. For example, if your xml is semi-structured and has mixed nodes, the above query might return results you didn't expect. The following query will return the element foo. That is because foo has two text() nodes and the = is the existential operator. Again, this could be what you want anyway. Also, this query doesn't find the attribute bar.

select convert(xml, '<foo bar="abc">abc<bar/>abc</foo>').query('//*[text() = "abc"]')

However, depending on the structure of you XML, this might be fine.

If you want to include elements who's attributes match the target value, you can do the following:

select convert(xml, '<foo bar="abc"/> <baz>abc</baz> <foo bar="not"/>').query('//*[text() = "abc" or @.* = "abc"]')

Regards,

Galex

is there any editable parameters?

i have written a function in report properties, but error returns when i
tried to add 1 to a report parameter, saying that parameter.value is
read-only.
anyways to have editable parameters'
thanks in advance~I have asked almost the same question a few days ago. Apparently no one can
tell us if this can be done.
"Jasonymk" wrote:
> i have written a function in report properties, but error returns when i
> tried to add 1 to a report parameter, saying that parameter.value is
> read-only.
> anyways to have editable parameters'
> thanks in advance~

Is there an SQL command for this?

Okay I have a column

Red
Red
Blue
Yellow
Blue
Blue
Blue
Blue

I want to return the value that appears most i.e. in this case Blue.

Thanks

BenThere is probably a more efficient way...

SELECT TOP 1 X, Count(*)
 FROM table
 GROUP BY X
 ORDER BY Count(*) DESC