searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005?
e.g., I want to do this:
select id, data, whatever, dbo.fnMyFunction(id, whatever) from table
and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction
possible? easier way? thanks in advance...
Hi,
We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.
Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),
As
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Return
End
This Works Fine :-
And Gives The Reqd. Results.
But,
If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.
Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int)
As
Begin
Declare @cnt Int
Set @cnt = 1
While @cnt <= 2
If @cnt =1
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Set @cnt = @cnt + 1
End
If @cnt =2
Begin
Insert @k2
Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
On A.intcustomerid = B.intcustomerid
Where Chremail <> ' ' And Chremail Is Not Null
And Intpreferenceid In (6,7,2,3,12,10)
Set @cnt2 = @cnt2 + 1
End
End
Return
End
Can We Return Two Tables Or Is It Not Possible ?
Pls Chk Into This And Tell Me.
Thanks.
I have created a function to return values, which works fine, but I can't do calculations in it.
CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )
I need to do calculations on market value based on the default date.
If default date isn't specified then it should be 100% of par amount.
If default date is less than one year ago - 65% of the par_amount.
If default date is one or more years ago - 0.
I have no idea about how to do this and everything I try wont work.
I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.
CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int
SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID
SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID
SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)
If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END
insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)
RETURN
END
I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
hi can anyone please help me
i have a stored procedure ,can i put the value of this into a variable......????
I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ...
example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) )
and ans of this are
NL
UK
IN
GN
JK
then how can i put this into a variable like
Declare @PortfolioName as varchar(250)
set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) )
.......
and my ans is
@PortfolioName = 'NL','UK','IN','GN','JK'
now can i make a function which returns 'NL','UK','IN','GN','JK'
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
Thanks in advance for your comments.
I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.
So my result should look something like this:
EEID| DepFirstName| DepLastName| DepRelationship| DepFirstName| DepLastName| DepRelationship
121 Billy Larson Spouse Alison Larson Child
How do I do this with SQL?
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?
View 4 Replies View RelatedHow to return multiple values from stored procedures to reports in sql server 2005
View 5 Replies View RelatedI have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.
Currently negative numbers are being returned because DateTime1 < DateTime2 or DateTime1 > DateTime2 .
The DateTime1 and DateTime2 values are dynamic and either of them can be bigger than the other.
Any query solution so that always positive value is returned.
Very basically, I need to return a result set based on another value and only if there are multiples of that other value.
Example. select * from mytable
Returns (--column separater)
John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4
So I want a query that would return only
John---1---1
John---1---1
John---3---3
John---3---3
The query cannot be something as simple as this:
Select * from mytable where John=1 or John=3
I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.
Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies View RelatedI have a table called tableA and i am fetching 10 rows from table.
select dept_id from tableA where branch = 'Chennai';
I got 10 records.
dept_id
-------
001
002
003
004
005
so n.....
Now i want to pass these dept_ids dynamically to a function parameter.
ie. exec function_name (@dept_id).
How do i write a function?
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
[code]...
Is it possible to pass multiple values to a TVF, such as using an IN clause?
View 6 Replies View Related
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies View RelatedIm a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID: Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)I cant seem to accomplish this. It returns nothing. Please help.TIA,Stue<code>Function Get_AttendID(ByVal strAttendID As String) As SqlDataReaderDim connString As String = ConfigurationSettings.AppSettings("ClassDB")Dim sqlConn As New SqlConnection(connString)Dim sqlCmd As SqlCommandDim dr As SqlDataReader
sqlConn.Open()Dim strSQL As String = "Select AttendID from attendees Where FirstName=@FirstName and LastName=@LastName and classbegdt = @classbegdt and survey = '0'"
sqlCmd = New SqlCommand(strSQL, sqlConn)
sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@FirstName").Value = tbFirstName.TextsqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@LastName").Value = tbLastName.TextsqlCmd.Parameters.Add("@classbegdt", SqlDbType.DateTime, 8)sqlCmd.Parameters("@classbegdt").Value = calBegDate.SelectedDate.ToShortDateStringdr = sqlCmd.ExecuteReader()dr.Close()sqlConn.Close()
Return dr
End Function</code>
I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.
Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.
You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQLData" instead of "D:MSSQLData" I then supply the backslash in the SET statement. I assume that this has something to do with my question.
Any suggestions? Thank you.
HERE IS T-SQL FOR THE FUNCTION
IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
BEGIN
DROP FUNCTION fn_sqlmgr_get_mdf_filepath
END
GO
CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS
BEGIN
--Extract the file path for the database MDF physical file.
SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1
SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('', REVERSE(@MDF_FILE_PATH)))
RETURN @MDF_FILE_PATH
END
HERE IS THE TEST I AM USING AGAINST THE FUNCTION
SET NOCOUNT ON
DECLARE
@MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database.
SELECT @MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH )
PRINT @MDF_FILE_PATH
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script.
This is my SP
CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)
AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)
select @@identity
return
GO
I tested in query analyser, and it works fine, so It must be my code. this is my function
Sub Insert_pic(sender as object, e as eventargs)
Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))
SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)
SQLCommander.Commandtype = Commandtype.StoredProcedure
SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)
'// this section not working right, it wont write return id
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue
response.write(SQLCommander.Parameters("ReturnValue").Value)
SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()
End sub
Can anybody see anything I missing? I appreciate any imput
Hii every one
When i use the function of (select) from the data bass it return dataset or some thing else
But I need it to return string or the data element which in the query not all the query
like
I dont need that
_____________
| Id | Name |
-----------------
| 1 | Bill |
--------------------
I dont need All of that to display But I need to display the name only in Label or textbox
like
Bill
Thanks
Maro
Hi,
I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).
Is there a common way to avoid this?
Thanx
Dear all,
can i write a table return function like this?
create function my_function(
returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME)
insert into @mytable
select col1,col2,col3,col4 from tab1
go
select col1,col2,col3,col4 from tab2
go
select col1,col2,col3,col4 from tab3
go
select col1,col2,col3,col4 from tab4
go
return
end
am i doing correct?
what i'm expecting from this function is, i need all the data from select statements should be inserted into one table.
please guide me in this regard
Vinod
Even you learn 1%, Learn it with 100% confidence.
//this is the function
double sem_3 ( double gpa_sem_1, double gpa_sem_2 )
{
int back_3;
char grade [2];
double score = 0;
double gpa_sem_3;
double cgpa_sem_3;
double problem_solving_and_programming;
double fundamentals_of_finance;
printf("FCCS1013 Problem Solving and Programming : ");
scanf("%s",&grade );
score = grade_selection( grade );
problem_solving_and_programming = score * 3;
printf("FBFF1013 Fundamentals of Finance : ");
scanf("%s",&grade );
score = grade_selection( grade );
fundamentals_of_finance = score * 3;
gpa_sem_3 = ( ( problem_solving_and_programming + fundamentals_of_finance ) / 6 );
cgpa_sem_3 = ( ( gpa_sem_1 + gpa_sem_2 + gpa_sem_3 ) / 3 );
printf("GPA = %.4f", gpa_sem_3);
printf("CGPA = %.4f", cgpa_sem_3);
printf("press 0 to go back to option menu: ");
scanf("%d", &back_3);
if( back_3 == 0 )
{
system("cls");
main();
}
else
{
printf("invalid option ");
}
return gpa_sem_3;
}
--------------------------------------------------------------------------------------------------------------------------------------------
//this is the main program(part of it only)
case 1:
system ( "cls" );
gpa_sem_1 = sem_1( );
break;
--------------------------------------------------------------------------------------------------------------------------------------------------
// why doesn't the error promp when the user enters an invalid entry? in the codes below.
double grade_selection ( char grade[] )
{
double score = 0;
if (strcmp( grade, "A+" ) ==0 || strcmp( grade, "a+" ) == 0)
{
score += 4.00;
}
else if (strcmp( grade, "A-" ) == 0 || strcmp( grade, "a-" ) == 0 )
{
score += 3.70;
}
else if (strcmp( grade, "B+" ) == 0 || strcmp( grade, "b+" ) == 0 )
{
score += 3.30;
}
else if (strcmp( grade, "B" ) == 0 || strcmp( grade, "b" ) == 0 )
{
score += 3.00;
}
else if (strcmp( grade, "B-" ) || strcmp( grade, "b-" ) == 0)
{
score += 2.70;
}
else if (strcmp( grade, "C+" ) || strcmp( grade, "c+" ) == 0 )
{
score += 2.30;
}
else if (strcmp( grade, "C" ) || strcmp( grade, "c" ) == 0 )
{
score += 2.00;
}
else if (strcmp( grade, "D" ) || strcmp( grade, "d" ) == 0 )
{
score += 1.00;
}
else if (strcmp( grade, "F" ) || strcmp( grade, "f" ) == 0 )
{
score += 0.00;
}
else
{
printf( "invalid grade" );
printf( "please enter again: " );
getchar( grade[2] );
}
return score;
}
-------------------------------------------------------------------------------------------------------------
thanks.
Hi,
I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.
How can I solve this problem and return varchar(MAX)?
Thanks
Hi guys!
the problem is this:
I have a procedure that execute a function and i want to catch the return function value but i'm getting a null value.
I'm using:
exec @a = <function>
but the @a variable is allways null...
If i exec the function directly, i get the correct value.
What am i doing wrong?
Thanks in advance.
I have a function that is using a select case decision structure to arrive at a return value:
[dbo].[fn_Window]
(@Start AS INT,
@End AS INT) RETURNS VARCHAR(5)
AS
BEGIN
SELECT
CASE
/*** Pick up the extended periods here ***/
WHEN @Start = 1 AND @End = 1 THEN 'TRUE'
ELSE 'FALSE'
END
END
**** from here how do I get the function to return true or false? ****
Can I assign the select case result to a variable? If so how?
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz