What is the correct syntax for dropping a column if it exists?
Cant seem to get it to work. This is SQL 2005 if it makes a difference.
ALTER TABLE Reports_PI_Recent
IF EXISTS (DROP COLUMN RollingAverage )
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
View 2 Replies View RelatedI have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
and (r.id = 'anything')
Hi.
This is a Stored proc I am working on
I dont know much about stored procs.
This is what I am trying to achieve
The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetNextAction]
(
@Code char(10),
@Track varchar(30),
@NextAction ntext output
)
AS
BEGIN
SET NOCOUNT ON;
Declare @ID int;
@ID = Select Sequence from [dbo].[Track] where Code=@Code;
Declare @nextCode varchar;
@nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1);
@NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode;
return @NextAction;
END
GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this.
Thanks
hi there
RS2005 - Layout tab
I have a field that says
txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))
I want another field that says
txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))
obviously this is going to error
how would I do this please
thanks
select CONVERT(VARCHAR, stdate, 'YYYY') startyear
from tbl_emp
i want output : 2007
How can i get?
If someone can show me the correct syntax for the right side of the code below, I'd appreciate it.
srcMoreInfo.InsertParameters("Address").DefaultValue = DirectCast(cuwMoreInfo.FindControl("txtAddress"), TextBox).Text
CREATE PROCEDURE test1
@spname nvarchar(20),
@tblname nvarchar(50)
AS
declare @sqlrun varchar(8000)
set @sqlrun = "CREATE PROCEDURE @sp_name AS CREATE TABLE @tblname
(MsdsId [nvarchar] (5),
IngredId [nvarchar] (2),
Agency [nvarchar] (10),
AgencyText [nvarchar] (4) ,
nName [nvarchar] (40))"
eXEC sp_ExecuteSql @sqlrun
WHATS WRONG IN THIS CODE ?
---------------------------------
PS: I'm getting this error:
-------------------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES
XYZ Company
ABC Limited Liability Company
ZZZ Corporation
KKK Inc.
ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS
Company
Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES
XYZ Company
ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT *
FROM [Names of Companies]
WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
Can anyone recommend a better way to do this?
Thanks for all your help!
Somehow I'm not getting dtsrun to work.
The server is local
The name of the package is ImportHosttrncs. It is saved under Local Packages.
There is no password.
Is this syntax correct?
dtsrun /Slocalhost /PImportHosttrncs
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
View 5 Replies View Related I try to call the storeproc to perform task by allowing only input 6 of length. The syntax over here might not be relevant, im seeking for correct syntax.
webform
Dim connString2 As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection2 As New SqlConnection(connString2)
Dim test01 As IntegerDim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2)
myPuzzleCmd2.CommandType = CommandType.StoredProcedure
Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6) < -------- allow 6 letters in length to be input
retLengthParam.Direction = ParameterDirection.InputmyPuzzleCmd2.Parameters.Add(retLengthParam) Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30)
retRandomCode.Direction = ParameterDirection.Output
myPuzzleCmd2.Parameters.Add(retRandomCode)
TryDim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader()
myPuzzleCmd2.ExecuteNonQuery()
Catch ex As Exception Response.Write("sp value : " & retRandomCode.Value)
Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1)
iRandomCode(1) = Convert.ToString(retRandomCode.Value)myPuzzleCmd2 = Nothing
Session.Remove("RandomCode") HttpContext.Current.Session("RandomCode") = iRandomCode(1) Response.Write(Session("RandomCode"))
Finally
myConnection2.Close()
End Try
End Using
StoredProcedure ALTER PROC [dbo].[GetRandomCode]
@Length TINYINT,
@RandomCode VARCHAR(30) OUTPUT
ASDECLARE
@Chars2BUsed VARCHAR(30),
@LookAt TINYINT,
@iCnt INT,
@CharFound CHAR(1)
SELECT @Chars2BUsed ='ABCDEFGHJKLMNPQRTUVWXYZ2346789'
SELECT @RandomCode = ''SELECT @iCnt = 1 WHILE @iCnt<=@Length
BEGINSELECT @LookAt = FLOOR((RAND()*LEN(@Chars2BUsed))) + 1
SELECT @CharFound = SUBSTRING(@Chars2BUsed, @LookAt, 1)IF CHARINDEX(@CharFound, @RandomCode)=0
BEGINSELECT @RandomCode = @RandomCode + @CharFound
SELECT @Chars2BUsed = REPLACE(@Chars2BUsed, @CharFound, '')SELECT @iCnt = @iCnt + 1
END
END
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
View 4 Replies View RelatedI am using @strsql to construct the where condition for a select query.
Can you please correct my syntax.
DECLARE @strsql nvarchar(2000)
IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if
If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
ELSE
strsql = wHERE ProjNO =@ProjNO
END IF
END IF
Thank you very much.
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
View 3 Replies View RelatedI have a interger stored in x.
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number).
I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance.
The database table is called "friday" and the one and only column is called "Tickets".
Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn;
conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***");
conn.Open();int count = -1;
SqlCommand cmd = new SqlCommand("select Tickets from friday", conn);
count = (int)cmd.ExecuteScalar();if (count > 0)
{
string updateString = @"
update friday
set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString);
cmd2.Connection = conn;
cmd2.ExecuteNonQuery();
}
else
{
}
conn.Close();
}
I have the following .bat file that I call to register users to my MSDE instance...
echo osql -E -S %COMPUTERNAME%OfficiumInstance -Q "sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_grantdbaccess '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_addrolemember 'db_owner', '" + %COMPUTERNAME% + "ASPNET'"
My question is how do I correctly append %COMPUTERNAME% as a string to
I have tried using + signs...
"sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
But that doesn't work.
I have looked for .bat file tutorials on the web but can't find the correct way to do this.
Greg
I am having trouble finding the correct syntax to access a variable. I have a variable defined in the Variables window: The variable name is formatedDate. The DataType is String.
I am successfully setting the value of the variable in a Execute SQL Task. The SQL is as follows:
SELECT LEFT(CONVERT(VARCHAR, MAX(ReportDate), 120), 10) as formatedDate
from DimReportDates
The Result Set is set to “Single Row” and properly set up.
No problem so far. I can see with a watch that the variable has the correct value, something like:
‘2015-09-30’
Now, in a subsequent step, a Data Flow Task, I want to access the variable. Actualy it is in the SQL statement of a OLE DB source in the Data Flow… I have the following:
Declare @sDate smalldatetime
SELECT @sDate = xxxxx
I have tried several things substituting xxxxx above, but nothing seems to work. One variation was
@[User::formatedDate])
Another was
((DT_WSTR, 10) @[User::formatedDate]).
I think I’m close, but just can’t get it. What is the correct syntax.
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
View 7 Replies View RelatedCREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);
For some reason the above statements are giving me syntax errors?
I want to create table only if it does not already exist.
Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?
thx in advance .
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
Hi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
I've created C#.net program (behind code style).
when I run it in Internet explorer, the following error occurs in IE window.
pls instruct me how to handle and correct this error.
And how to initialize the connectionstring... Great thank!
Server Error in '/' Application.
--------------------------------------------------------------------------------
The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values
into another table. IN VB this was simple but the while in TSQL seems
a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays
--insert values
insert into table values (value1, value2)
--then increment counters and repeat
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped
How do you think..and why ?
thanks
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped
How do you think..and why ?
thanks
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data.
I ONLY WANT
a. records that are only in A but not in B
b. records that are only in B but not in A
______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS
AS
BEGIN
DECLARE @Rowcount AS INT
DECLARE @First_Name AS VARCHAR(50)
DECLARE @Last_Name AS VARCHAR(50)
DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess
SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK
SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------'
PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ +
CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Prowess WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Sdk WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END
DROP TABLE #Prowess
DROP TABLE #Sdk
END
declare @var varchar(50)
set @var= 'COLUMNNAME'
select ID, a.@var , b.@var
from rooper a
join jim_rooper b on b.id = a.id
join b_rooper bb on bb.id = a.id
where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...