Database

Maciej Los wrote:

too many subqueries.

ALTER PROCEDURE [dbo].[UpdateSurvey]
    -- Add the parameters for the stored procedure here
    @ID int,@response nvarchar(50),@buildingpoint  nvarchar(50),@ID1 int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

if(@ID1=1)
Begin
UPDATE survey  SET
 ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End

Else If(@ID1=2)

Begin
UPDATE survey  SET
 ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End

Else If(@ID1=3)

Begin
UPDATE survey  SET
 ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=4)

Begin

UPDATE survey  SET
 ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=5)

Begin


UPDATE survey  SET
 ques_3 = (select response from surveyanswers t2 where id=@response),q3buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END

Else If(@ID1=6)

Begin
UPDATE survey  SET
 ques_4 = (select response from surveyanswers t2 where id=@response),q4buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=7)

Begin
UPDATE survey  SET
 ques_5 = (select response from surveyanswers t2 where id=@response),q5buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=8)

Begin
UPDATE survey  SET
 ques_6 = (select response from surveyanswers t2 where id=@response),q6buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
   END

Else If(@ID1=9)

Begin
UPDATE survey  SET
 ques_7 = (select response from surveyanswers t2 where id=@response),q7buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=10)

Begin
UPDATE survey  SET
 ques_8 = (select response from surveyanswers t2 where id=@response),q8buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID


output


exec UpdateSurvey 5,5,5,2(id,response,buildingpoint,@ID1=2)
but instead of updating @ID1=2 ,it updates @ID1=5 .BUT I want to update @ID1=2 query .Is there any solution for this

ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_VendorLevelId DEFAULT (0) FOR VendorLevelId;

UPDATE dbo.mytable
SET VendorLevelId = 0
WHERE VendorLevelId Is Null;

ALTER TABLE dbo.mytable
ALTER COLUMN VendorLevelId int NOT NULL;

CREATE TABLE dbo.doc_exy (column_a INT )  ;
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO

SELECT * FROM  dbo.doc_exy 

10

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO

SELECT * FROM dbo.doc_exy

10.00

DROP TABLE dbo.doc_exy ;
GO

string connStr = "server=127.0.0.1;uid=DogzBolx;pwd=password;database=imagecatalog;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
string sql = "select * from data order by count desc;";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable data = new DataTable();
da.Fill(data);
foreach(DataRow row in data.Rows)
{
    string md5 = Convert.ToString(row[0]);
    int count = Convert.ToInt32(row[1]);
    Console.WriteLine(md5 + " " + count);
}
conn.Close();

SET CONCAT_NULL_YIELDS_NULL ON;
GO

CREATE FUNCTION Function_StripHTML(@Text NVARCHAR(MAX), @BreakLine BIT)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @XML AS XML
	
	IF @BreakLine = 1
	BEGIN
		SET @Text= REPLACE(REPLACE(REPLACE(@Text,'<br>', CHAR(13)), '<br/>',CHAR(13)), '<br />',CHAR(13))
	END
	ELSE
	BEGIN
		SET @Text= REPLACE(REPLACE(@Text, '<br/>','<br>'), '<br />','<br>')
	END
	
	SET @XML = CAST(('<x>' + REPLACE(REPLACE(dbo.Function_RemoveInvalidCharacters(@Text), '&', '&'), '<', '</x><x>')+'</x>') AS XML)
	
	SELECT
		@Text=
		(
			SELECT
				CASE WHEN LEFT(N.value('.', 'VARCHAR(MAX)'), 3) = 'br>' THEN
					'<' + N.value('.', 'VARCHAR(MAX)')
				ELSE
					STUFF
					(
						N.value('.', 'VARCHAR(MAX)')
						, 1
						, CHARINDEX('>', N.value('.', 'VARCHAR(MAX)'))
						, ''
					)
				END
			FROM
				@XML.nodes('x') AS T(N)
			  FOR XML PATH('')
			, TYPE
		).value('.', 'VARCHAR(MAX)')
	
	RETURN LTRIM(RTRIM(@Text))
END
GO