Can't Get Left() Function To Work With A Variable.
Jan 14, 2008
I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable.
I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable.
Anyone knows why this isn't working?
This is how I have the code:
SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1)
the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this:
SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.
View 5 Replies
ADVERTISEMENT
May 23, 2007
Hello to all,
I hope that somebody can help me.
I have written a sql query to search Partner. I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table.
Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0
and charindex(@IDO,B.RelationshipIDs) > 0
Using this query i get nothing.
I try to use constant in charindex and i get result.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0
and charindex('4088',B.RelationshipIDs) > 0
So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?
Thanks
Best Regards
Pinsha
View 1 Replies
View Related
May 25, 2012
I have tables A and B.
Table A has data, but B may have or not.
I have to get -- at least! -- the A table data.
It works when I dont have the where clause:
select * from A left outer join B on A.IDUser = B.IDUser
But it doesn't when I try to use the WHERE clause:
select * from A left outer join B on A.IDUser = B.IDUser
where A.IDUser = 2
View 3 Replies
View Related
Apr 7, 2008
i have a query
select message from ticket
but i want to only get the first 100 characters
select left(message,100) from ticket
is giving me an error - please advise?
View 3 Replies
View Related
Jun 29, 2006
I normally use MS ACCESS vs MS SQL,, which has a left() and right()function. I need to use MS SQL for this project but I am not familiarwith it. I have read a few books, but can not figure out how to dothis. Please help.If I need to compare the first 4 letters of a field, with the firstfour letters of another field, how can I do this?Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4)(MS SQL does not have left() and right() functions)Please help.In addition, I have a CSV file with data like 10.20, which I importinrto a numberic field. Unforunately the value gets changed to 10.It's seems to get rounded. How can I fix this.The import SQL I use is....BULK INSERT dbo.tableFROM 'c:MYDATA.CSVWITH(FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = '')Thank you in advance!!!
View 9 Replies
View Related
Nov 3, 2006
select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)Hello WorldThat works fine.However I got an error message:select left('Hello World Ok',charindex('/','Hello World Ok')-1)Instead of:'Hello World Ok'I get:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.Microsoft Doc incorrectly says:"LEFT ( character_expression , integer_expression )integer_expressionIs a positive whole number. If integer_expression is negative, a nullstring is returned."Is there an easier solutoin using left or any other string functioninstead of using a case statement?Also, charindex('/','Hello World Ok') should return NULL instead 0 sothat we can use isnull function.Thanks.
View 2 Replies
View Related
Dec 6, 2007
How do you combine a left and a right function. I have tried different variation and none seemed to work. What I want to accomplish is 6 spaces to the right and 2 spaces to the left.
View 3 Replies
View Related
Jun 18, 2006
Hello folks,Old programmer learning new tricks.I think similar issues have been posted, but I would like to ask some helpwith the following code...select [t1.*],[t2.userid] as [userid2],[t2.approved],[t2.type]from [tblMembers] as t1 left outer join [tblProfiles] as t2on [t1.userid]=[t2.userid]where ...I get the following error...[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name't1.userid'.....but there is a [userid] in both tables. Do I have to make them keys orsomething? And I need to do work (add and update) on both tables after thevalues are returned.Also, how bad is my syntax? If you could give advice on removing extraneousbrackets and I would like to not have to use the "as" clause. I really justwant to keep it simple. Whatever works though.Thanks
View 3 Replies
View Related
Dec 22, 2006
Anyone know the reaon the Left function was left out of the list of string functions in the Expression Builder?
Danno
View 6 Replies
View Related
Oct 15, 2007
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
View 1 Replies
View Related
Mar 30, 2015
SELECT LEFT(InstanceName,CHARINDEX(' ',InstanceName)-1)
FROM ServerDB
Returns the following error.
Invalid length parameter passed to the SUBSTRING function.
View 2 Replies
View Related
Jan 1, 2015
I found something very strange...stufff function working with self join but not working with left or right join,. I have a table
**Id name**
1 samar
1 Harry
2 jack
I want the output as
**Id name**
1 samar Harry
2 jack
The below query works fine with self join
Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)
From #test b
Group by id
But when i do right join i get error _ invalid object name `b`. ....
Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)
From #test b
Group by id
View 4 Replies
View Related
Oct 18, 2007
Hi,
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant
than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
Anyone have an idea why?
Mathieu Desbiens
View 1 Replies
View Related
Dec 6, 2004
Hello!
I am trying to run a query on an SQL-server database:
SELECT (Left(Comments, 20)) As shortComment FROM myTable
The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype...
How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY?
noccy
View 1 Replies
View Related
Jul 22, 2015
when I am trying to perform below query,
Â
 INSERT INTO EMPLOYEE
  SELECT TOP 100 *
 FROM EMPLOYEE_LANDING;
I am getting Invalid length parameter passed to the LEFT or SUBSTRING function.
View 3 Replies
View Related
Jan 11, 2005
Hi! all
Can any body help me straighten this function. Just need to get the diffrence between the two time frame in Minutes
CREATE proc setup_DataReport
as
Declare @rc1 int
Declare @rc2 int
Declare @result int
SELECT @rc1 =count(AL_Coming)
FROM Alarm_Log
where convert(char(5),AL_Coming,14) between '09:00' and '10:00' AND
AL_State = 0
SELECT @rc2 = count(AL_Coming)
FROM Alarm_Log
where convert(char(5),AL_Coming,14) between '09:00' and '10:00' AND
AL_State = 1
select @rc1 as a
select @rc1 as a
set @result = sum(@rc1 - rc2)
print @result
go
View 2 Replies
View Related
Mar 16, 2006
I'm trying to do something like this SQL statement. I have a table with a field of date/times.
i.e - 2/27/06 9:55:95 PM Basically there are multiple entries in the table per day.
I want to count the records for particular (hence the CAST) and output the count.
Here it was I had just to see if SQL would work in ASP.NET :
SELECT COUNT(*) AS Expr1FROM dbo.tblActiveDrumsWHERE [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0' OR NCTimeStamp - [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0'
It didn't like the AS references?
Any help would be appreciated.
View 2 Replies
View Related
Jan 10, 2008
I have a variable that is created dynamically that will be used as an input for a sql statement.
When I display the variable in a text box it looks like this:
(PROP_CLASS IN ('" & 100 & "','" & 101 & "' ))
The following works when I hard code the values into the SQL statement:
strSQL = "SELECT * FROM web_transfer WHERE (PROP_CLASS IN ('" & 100 & "','" & 101 & "' )) AND ........."But I get no values returned when I use the variable directly in the sql: strSQL = "SELECT * FROM web_transfer WHERE (PROP_CLASS IN (" & propertyClass & ")) AND ..........." The propertyClass variable is the text string “(PROP_CLASS IN ('" & 100 & "','" & 101 & "' ))“. How do I add this variable to the sql string so that it is recognized. I guess that it is adding some hidden characters. Any help please?? I am a newbie to this, s
View 5 Replies
View Related
Jan 26, 2006
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
Thanks!
View 2 Replies
View Related
Oct 24, 2015
playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model. I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.
View 4 Replies
View Related
Aug 29, 2007
Hi
I am writing a application using the ODBC Calls to connect to MSSQL Database 2005 SP2.
I am using the Microsoft Visual Studio 6.0 for compilation
I have defined the UNICODE macro in the project settings.
Now when i am trying to connect to MSSQL Database using this call below
retcode = SQLConnect(dbc, (SQLTCHAR*) dsn, SQL_NTS,
(SQLTCHAR*) loginname, SQL_NTS,
(SQLTCHAR*) password, SQL_NTS);
I give a wrong username .
Then i use the function
SQLGetDiagRec to collect the error
void extract_error(char *fn,SQLHANDLE handle, SQLSMALLINT type)
{
SQLINTEGER i = 0;
SQLINTEGER native;
SQLTCHAR state[ 100 ] ;
SQLTCHAR text[256];
SQLSMALLINT len;
SQLRETURN ret;
memset(state,0,sizeof(state));
memset(text,0,sizeof(text));
fprintf(stderr,
""
"The driver reported the following diagnostics whilst running "
"%s",
fn);
do
{
ret = SQLGetDiagRec(type,
handle,
++i,
state,
&native,
text,
sizeof(text),
&len );
if (SQL_SUCCEEDED(ret))
_stprintf(_T("%s:%ld:%ld:%s"), (const TCHAR *)state, i, native, text);
_stprintf(_T("Error code %s "),(const TCHAR *)state);
}
while( ret == SQL_SUCCESS );
}
Now if i check the values of "state" and "native" i get it as 0.
If i use non unicode (i.e. remove the UNICODE macro) it works fine.
Best Regards
Manoj
View 1 Replies
View Related
Apr 7, 2006
Has someone managed to pass successfully a variable from a parent package to a child package? I€™ve tried a zillion permutations and I can€™t get it to work. The strange thing was that I was able to successfully do this with pre-RTM builds. Basically, what I am trying to do is:
The parent package has a variable, e.g. ExecutionID which I set using a script to System::ExecutionInstanceGUID. I verified that the variable is set correctly by dumping it to a SQL Server table.
I created a child package variable with the same name.
In the child package, I€™ve created a parent package configuration that points to the ExecutionID variable.
I am trying to read the variable in a Derived Column Task in which I have a column linked to @ExecutionID.
This doesn€™t work. Step-by-step instructions from someone who managed to concur this will be greatly appreciated.
Oh, I didn€™t have any luck hitting a breakpoint in a script task inside a child package with both in and out of process execution also.
View 1 Replies
View Related
Feb 7, 2006
Dear Community,We have a problem with null-Bytes in varchar-Columns, which are nothandled correctly in our application. Therefor we try to filter themout using the Transact-SQL REPLACE function.The Problem was, that the REPLACE-function didn't behave the way weexpected.Following Example demonstrates the behavior:declare @txt varchar(512)declare @i intset @txt = 'hello ' + char(0) + 'world'print @txtset @i = 1while @i <= len(@txt)beginprint str(@i) + substring(@txt, @i, 1)set @i = @i + 1endprint 'Length: ' + str(len(@txt))print 'trying to replace null-byte:'print replace(@txt, char(0), '*')print 'replace Letter h'print replace(@txt, 'h', char(39))-- end exampleOutput:hello1h2e3l4l5o678w9o10r11l12dLength: 12trying to replace null-byte:*replace Letter h'elloThe Null-Byte replace destroys the whole string. This behavior occursonly on some of ourdatabases. The others work correctly.Is it possible that it depends on some server setting?ThanksEnno
View 5 Replies
View Related
Jun 4, 2008
declare @number int
set @number = 100
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(int, @number, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
Is there any way i can use variable inside the identity function like in the above example?.
Is there any other alternative?
Thanks in advance
View 6 Replies
View Related
Feb 7, 2006
Dear all,
Can someone help me with the following function? I would like to use a table name as a variable.
Thanks in advance!
CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric
if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end
if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end
end
return @aantal
END
View 2 Replies
View Related
Aug 30, 2006
Hi!
I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?
Thank you!
View 1 Replies
View Related
May 1, 2006
I could successfully modify the package level variable using a script component (Control Flow Level) and execute the data flow task after this script component. The OLE DB Command has one parameter for which I'm using one of the user variable. Here's the SQL statement.
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM d_Time_School_Year
WHERE (Year_Key = ?)
This works fine. But I want to pass the year_key to a function which accepts a parameter. The SQL should be like this
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM fn_TimeDimension (?)
But SSIS doesn't like this. When I click on parameters command button I get and error like this
"Parameters cannot be extracted from the SQL command. The provider might not help.........
Syntax error, Permission Violation, or the non-specific error(Microsoft SQL native Client)"
Any clue how to utilize the variables in a SQL which gets data from a function instead of a table?
Thanks
Jemini Joseph
View 10 Replies
View Related
Oct 28, 2015
I have a table PLACE with a character column CODE
[Code] [nchar](4) NULL
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
   Â
SELECT Â RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) Â Â Â
  FROM [Place]
  WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
View 10 Replies
View Related
May 12, 2007
Hi
I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime.
I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor.
Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher?
Thanks for the look see...
******************************
ALTER FUNCTION [dbo].[convdate]
(
@indate nvarchar(10)
)
RETURNS nvarchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @outdate nvarchar(10)
set @outdate =
substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+
substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+
substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2)
RETURN @outdate
END
********************************
And the error...
expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid.
Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
View 3 Replies
View Related
Jul 30, 2007
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance
View 3 Replies
View Related
Aug 29, 2013
I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine
select @columnname,0,0,0,0
from temp_prt
it gives me my expected output
mtr_5120,0,0,0,0
mtr_3247,0,0,0,0
mtr_5160,0,0,0,0
etc........
now i want to get the min of each column name like so
select @columnname,min(mtr_5120),0,0,0
from temp_prt ------> this works for min(mtr_5120)
mtr_5120,34.5,0,0,0
now I want to generalize so I try to pass in the variable name and I do the following
select @columnname,min(@columnname),0,0,0
from temp_prt
(the columname (@columnname) exists in the table temp_prt)
but now i get an error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:
select @columnname,'min(' + @columnname + ')',0,0,0
from temp_prt
but i get the same error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.
View 2 Replies
View Related
Aug 11, 2006
is it possible to pass a variable to an identity funtion
example
declare @max_note int
select @max_note = max(key ) from notes
select m_key = identity( int, @max_note, 1),
name
into #prod_note
from prod_note
View 3 Replies
View Related
Feb 1, 2006
Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far
<script runat="server"">
Sub Page_Load
dim --all the variables for my sql connections--
dim ff1 As Date
then my sql connection and queries
sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel
This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table)
dim ff1 As string
ff1="30/01/2006"
but I need ff1 to be the current date and is I use ff1 As date it returns time and date
Is there any way to set ff1 to the current date in this format "dd/mm/yyyy"
Any help is greatly appreciated
View 2 Replies
View Related
